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 (the database is Oracle Database 11g).

When Oracle creates a histogram on a VARCHAR2 column it will only consider the first 64 characters of the column value in Oracle Database 12c or the first 32 characters of the column value in Oracle Database 11g. In other words, if the first 32/64 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?

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 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.