Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Any quick way to remove histograms?
Why spool, capture and then execute when you can do it all in one:
declare
l_ndv number; l_dummy number; l_srec dbms_stats.statrec;
for r in (select owner, table_name, column_name, null partition_name from DBA_TAB_COL_STATISTICS
histogram <> 'NONE' [ and possibly other criteria ] union all select owner, table_name, column_name, partition_name from DBA_PART_COL_STATISTICS histogram <> 'NONE' [ and possibly other criteria ] ) loop dbms_stats.get_column_stats ( ownname => r.owner, tabname => r.table_name, colname => r.column_name, partname => r.partition_name, stattab => null, statid => null, distcnt => l_ndv, density => l_dummy, nullcnt => l_dummy, srec => l_srec, avgclen => l_dummy); dbms_stats.set_column_stats ( ownname => r.owner, tabname => r.table_name, colname => r.column_name, partname => r.partition_name, distcnt => l_ndv);
and no fighting with quotes :-)
I believe if you have subpartitions you will need to export the statistics to a stattab table, manipulate the statistics there and then re-import as there is no set_column_stats for subpartitions.
At 06:23 PM 3/26/2007, Allen, Brandon wrote:
>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_at_gmail.com]
>Sent: Friday, March 16, 2007 4:43 PM
>To: breitliw_at_centrexcc.com
>Cc: Allen, Brandon; Oracle-L_at_freelists.org
>Subject: Re: Any quick way to remove histograms?
>
>Much simpler :) - interesting.
>
>
>On 3/16/07, Wolfgang Breitling <breitliw_at_centrexcc.com> 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_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.
>
>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
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 27 2007 - 08:19:36 CDT
![]() |
![]() |