How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?
Before answering this question, I want to provide a bit more background to this scenario. The person who submitted this question has a table with a VARCHAR2 column called ID whose data distribution is skewed. When the Auto Stats job kicks in for this table a histogram is automatically created on this column as it is used in nearly every SQL statement and it has a data skew. However, the values in the ID column are extremely long and the first 32 characters in each ID are identical.
When Oracle creates a histogram on a VARCHAR2 column it will only consider the first 32 characters in the column. In other words, if the first 32 bytes of the column values are identical all of the values end up in a single bucket of the histogram even though in reality the values are different and are suppose to be in different histogram buckets. The Optimizer can potentially misestimate the cardinality when using this histogram, which can result in suboptimal plan. Note that number of distinct values and other statistics will be accurate, only the histogram will have this issue. For this reason it would be better not to have a histogram on this particular column now or in the future. So how do you drop the existing histogram and stop it from being collected in the future?
The answer to this question depending on which version of the database you running, Oracle Database 11g or 10g and lower.
11G solution
=========
1. You should begin by dropping the histogram on the ID column. You can this by using the dbms_stats.delete_column_stats procedure and setting the col_stat_type parameter to HISTOGRAM.
BEGIN
dbms_stats.delete_column_stats(
ownname=>'SH', tabname=>'SALES', colname=>'PROD_ID', col_stat_type=>'HISTOGRAM');
END;
2. Use the new dbms_stats.set_table_pref procedure to set a specific value for the method_opt parameter for the table effected by this problem. The following value for the method_opt parameter tells Oracle to continue to collect histograms as usual on all of the columns in the SALES table except for the PROD_ID column, which should never have a histogram created on it.
BEGIN
dbms_stats.set_table_prefs('SH', 'SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
END;
/
3. The auto stats gathering job or your own statistics gathering commands will now use the table preference you set when it gathers statistics on this table and will no longer create a histogram on the ID column.
10G solution
=========
1. You should begin by dropping the existing statistics for the sales table. You can do this by using the dbms_stats.delete_table_stats procedure.
BEGIN
dbms_stats.delete_table_stats(ownname=>'SH', tabname=>'SALES');
END;
/
2. Next lock the statistics on the effected table using dbms_stats.lock_table_stats.
BEGIN
dbms_stats.lock_table_stats(ownname=>'SH', tabname=>'SALES');
END;
/
3. The auto-stats gathering job, which will now maintain statistics for all other tables but not the effected table.
4. Create a job of your own that will gather statistics on the effected table using the following command. This command will allow Oracle to automatically gather histograms on all of the other columns in the table but not on the ID column. Note you need to set the force parameter to true to override the locked statistics.
BEGIN
dbms_stats.gather_table_stats('SH', 'SALES',
method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 PROD_ID',
force => true);
END;
/
