| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_oneneck.com> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 16 2007 - 16:36:36 CDT
 |
 |