Re: Any quick way to remove histograms?

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

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l


Other related posts: