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: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Fri, 16 Mar 2007 20:40:03 +0100
Message-ID: <4ef2fbf50703161240p6629c99frda48a98edf37140b@mail.gmail.com>


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.

HTH
Alberto

-- 
Alberto Dell'Era
"Per aspera ad astra"


-- http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 16 2007 - 14:40:03 CDT

Original text of this message

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