Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Any quick way to remove histograms?

Re: Any quick way to remove histograms?

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 16 Mar 2007 15:36:36 -0600
Message-Id: <20070316213550.DD2A6648F7C@turing.freelists.org>


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



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
Received on Fri Mar 16 2007 - 16:36:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US