RE: Any quick way to remove histograms?

Nice.

FYI. An easy way to get the quotes right is instead of thinking about
it, use ||''''|| every time you want one.   (an escaped quote surrounded
by quotes).
It's also easier to read and keep track of where you are.

select 'execute dbms_stats.set_column_stats('||''''||owner
||''''||','||''''||table_name||''''||','||''''||column_name||''''||
',distcnt=>'||''''||num_distinct||''''||');' from all_tab_col_statistics
where histogram <> 'NONE';

Joel Patterson
Database Administrator
joel.patterson@xxxxxxxxxxx
x72546
904  727-2546

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Allen, Brandon
Sent: Monday, March 26, 2007 8:24 PM
To: Alberto Dell'Era; breitliw@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Any quick way to remove histograms?

At first I was thinking that Alberto's procedure would still be required
for retrieving the current stats and then resetting just the distcnt
value.  However, after thinking it through for a while, I realized what
you were probably both thinking the whole time, which is that with this
simplified method, you can just use a simple SELECT statement on
all_tab_col_statistics to retrieve the current num_distinct value and
plug it into the set_column_stats procedure for all columns that
currently have histograms, thereby creating a script that can be used to
very quickly remove all existing histograms.  It took me a while to get
all the single quotes just right, but here is the working version for
anyone else that might find it helpful:

select 'execute dbms_stats.set_column_stats(''' || owner || ''',''' ||
table_name || ''',''' || column_name || ''', distcnt=>''' ||
num_distinct || ''');' from all_tab_col_statistics where histogram <>
'NONE';

Sample Output:

----------------------------------------------------------------------
execute dbms_stats.set_column_stats('SCOTT','T','C1', distcnt=>'2421');
execute dbms_stats.set_column_stats('SCOTT','T','C2', distcnt=>'2425');
execute dbms_stats.set_column_stats('SCOTT','T','C3', distcnt=>'2421');


Thanks again to Wolfgang and Alberto!

Regards,
Brandon




-----Original Message-----
From: Alberto Dell'Era [mailto:alberto.dellera@xxxxxxxxx] 
Sent: Friday, March 16, 2007 4:43 PM
To: breitliw@xxxxxxxxxxxxx
Cc: Allen, Brandon; Oracle-L@xxxxxxxxxxxxx
Subject: Re: Any quick way to remove histograms?

Much simpler :) - interesting.


On 3/16/07, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
>
> I also did a quick check and just using
>
> exec
> dbms_stats.set_column_stats(user,'table_name',colname=>'column_name',d
> istcnt=>
> <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.

Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do
not consent to Internet email for messages of this kind. Opinions,
conclusions and other information in this message that do not relate to
the official business of this company shall be understood as neither
given nor endorsed by it.

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


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


Other related posts: