2012-01-25

how to delete histograms for a column

How to delete just histograms without regathering column statistics [ID 5579764.8]

Affects:

Product (Component)Oracle Server (Rdbms)
Range of versions believed to be affectedVersions < 11
Versions confirmed as being affected
Platforms affectedGeneric (all / most platforms affected)

Fixed:

This issue is fixed in

Symptoms:

Related To:

Description

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

Niciun comentariu:

Trimiteți un comentariu