Sometimes you want to drop the histogram of a particular table and dont want to generate a histogram for a particular column in the table. let’s see how we can achieve it

11G and the above solution

First of all, you can delete the existing histograms using the below command

BEGIN
dbms_stats.delete_column_stats(
ownname=>'SCOTT', tabname=>'TEST', colname=>'UNIQ_ID', col_stat_type=>'HISTOGRAM');
END;

Now you can use the new dbms_stats.set_table_pref procedure to set a specific value for the method_opt parameter for the table affected by this problem. You can achieve this using the below command

BEGIN
dbms_stats.set_table_prefs('SCOTT', 'EMP','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS   SIZE 1 UNIQ_ID');
END;
/

Now the 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 particular column

10G solution

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=>'SCOTT', tabname=>'TEST');
END;
/

Now. we dont have preferences in 10g, so we need to lock the statistics on the affected table using dbms_stats.lock_table_stats.

BEGIN
dbms_stats.lock_table_stats(ownname=>'SCOTT', tabname=>'TEST');
END;
/

Now your scheduled gather stats will not gather stats on this table. Now you can put another script to generate the stats on this table

BEGIN
dbms_stats.gather_table_stats('SCOTT', 'TEST',
method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 UNIQ_ID',
force => true);
END;
/

Here force option will let the command run the gather stats on this table.

How to check if the column is having histograms

select COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM from DBA_TAB_COL_STATISTICS where table_name='&table_name' and owner='&table_owner' order by column_name;

Histograms can be a powerful tool for improving query performance in Oracle Database, but they should be used judiciously. It’s important to monitor the performance of queries and adjust histogram settings as needed to achieve optimal performance.