Re: Any quick way to remove histograms?

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: alberto.dellera@xxxxxxxxx
  • Date: Fri, 16 Mar 2007 15:36:36 -0600

I also did a quick check and just using

exec dbms_stats.set_column_stats(user,'table_name',colname=>'column_name',distcnt=> <num_distinct>);

will remove the histogram without removing the low_value and high_value.

At 01:40 PM 3/16/2007, Alberto Dell'Era wrote:
On 3/16/07, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
Is there any faster way to remove histograms other than re-analyzing the
table?  I want to keep the existing table, index & column stats, but with
only 1 bucket (i.e. no histograms).

You might try the attached script, that reads the stats using
dbms_stats.get_column_stats and re-sets them, minus the
histogram, using dbms_stats.set_column_stats.

I haven't fully tested it - it's only 10 minutes old, even
if I have slightly modified for you another script I've used for
quite some time - and the spool on seems to confirmthat
the histogram is, indeed, removed, while all the other statistics
are preserved. I have also reset density to 1/num_distinct, that
is the value you get if no histogram is collected.


Wolfgang Breitling
Centrex Consulting Corporation

This email has been scanned by the MessageLabs Email Security System.
For more information please visit ______________________________________________________________________

Other related posts: