How To Quickly Add/Remove Column Statistics (Histograms) For A Column

In some situations it is necessary to quickly add or remove histograms from a particular column (also known as column statistics.)

This article shows how to accomplish this with one command.

The commands given in this article will not affect column statistics currently gathered for other columns, they will only touch the specified column.

Column statistics can be:
- so called minimal consisting of just 1 bucket (2 endpoints) with min/max value information required by the Optimizer
- histograms with between 2 and 254 buckets (3 and 255 endpoints respectively)
- absent with no column information recorded in the data dictionary (not normally recommended)

Assuming you are connected as the owner of the table.

To add column statistics use one of the following varieties:

• using up to the default number of buckets 75:
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name>');
• using the appropriate number of buckets for the data distribution (will be minimal when not skewed) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size skewonly');
• using maximum number of buckets (up to 254) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size 254');
• using up to specified number of buckets N (N is between 2 and 254) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size N');

• gathering statistics for multiple columns with different bucket sizes :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>');
• gathering statistics for multiple columns with different bucket sizes plus minimal information for the remaining columns:
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for all columns size 1 for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>');

To delete histograms from a column leaving behind base column statistics i.e. minimal information required for the Optimizer (this is for pre-Oracle11g versions:)

SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size 1');

To completely erase all types of column statistics/histograms for a column including minimal statistics:

SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>');

This is generally not recommended as the Optimizer needs at least the minimal (size 1) information.

In Oracle11g there is new syntax to delete the histogram for a column while still leaving behind the base column statistics:

SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>', col_stat_type=>'HISTOGRAM');

Additionally there is functionality to delete histograms for a partition:

exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>',
colname=>'<column_name>',partname=>'<partition_name>', col_stat_type=>'HISTOGRAM')

and to delete column histograms for the table and all its partitions:

exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>',
colname=>'<column_name>',cascade_parts=>TRUE,col_stat_type=>'HISTOGRAM')