Database Tuning‎ > ‎

How to delete just histograms without regathering column statistics --11g

posted Sep 13, 2010, 1:03 PM by Sachchida Ojha
Pre-11g There is currently no simple way to delete histogram statistics
leaving the column level statistics without re-analyzing.


This fix add a new argument (col_stat_type) to dbms_stats.delete_column_stats 
to provide the ability to delete histogram statistics only.  

col_stat_type can be ALL or HISTOGRAM:
 ALL is the current behavior which deletes all column statistics, 
 HISTOGRAM will only delete the histograms for the column.

eg: 
 To delete histograms for a partition:
  exec dbms_stats.delete_column_stats(ownname=>'DBA10',tabname=>'REF_PRC', 
   colname=>'ANLYS_DATE',partname=>'REF_PRC_200609', col_stat_type=>'HISTOGRAM')

 To delete a column histograms for the table and all its partitions:
  exec dbms_stats.delete_column_stats(ownname=>'DBA10',tabname=>'REF_PRC', 
   colname=>'ANLYS_DATE',cascade_parts=>TRUE,col_stat_type=>'HISTOGRAM')


Workaround
  Use get_column_stats to get all column stats, change the bucket count
  in srec (to reflect no histogram) and do set_column_stats
Comments