Re: drop a histogram on a single column

To make Greg's suggestion work, on 10.2.0.3 or .4, you need the patch for bug 5579764

**

On Mon, Dec 15, 2008 at 8:16 PM, Allen, Brandon <Brandon.Allen_at_oneneck.com>wrote:

> Taking my example one step further shows that the pre-11g trick suggested
> earlier does seem to work as expected. Note that the density is also
> updated to 1/num_distinct in the absence of the histogram, but I think that
> is to be expected:
>
> SQL> exec dbms_stats.set_column_stats('sys','t','owner',distcnt=>12);
>
> PL/SQL procedure successfully completed.
>
> SQL> select num_distinct, low_value, high_value, density, histogram from
> dba_tab_columns where table_name='T';
>
> NUM_DISTINCT LOW_VALUE
> ------------
> ----------------------------------------------------------------
> HIGH_VALUE DENSITY
> HISTOGRAM
> ---------------------------------------------------------------- ----------
> ---------------
> 12 4442534E4D50
> 564552544558 .083333333
> NONE
>
>
> Regards,
> Brandon
>
> -----Original Message-----
> From: Allen, Brandon
> Sent: Monday, December 15, 2008 12:09 PM
>
>
> Doesn't seem to work for me on 10.2.0.4:
>
> SQL> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
>
> SQL> create table t as select owner from dba_objects;
>
> Table created.
>
> SQL> exec dbms_stats.gather_table_stats('sys','t',method_opt=>'for columns
> owner');
>
> PL/SQL procedure successfully completed.
>
> SQL> select num_distinct, low_value, high_value, density, histogram from
> dba_tab_columns where table_name='T';
>
> NUM_DISTINCT LOW_VALUE
> ------------
> ----------------------------------------------------------------
> HIGH_VALUE DENSITY
> HISTOGRAM
> ---------------------------------------------------------------- ----------
> ---------------
> 12 4442534E4D50
> 564552544558 .000028752
> FREQUENCY
>
> SQL> exec
> dbms_stats.delete_column_stats('sys','t','owner',col_stat_type=>'HISTOGRAM');
> BEGIN
> dbms_stats.delete_column_stats('sys','t','owner',col_stat_type=>'HISTOGRAM');
> END;
>
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to
> 'DELETE_COLUMN_STATS'
>
>
> -----Original Message-----
> From: Allen, Brandon
>
> According to Metalink 5579764.8, it looks like it's only available in
> 11.1.0.6+.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Greg Rahn
>
> Version >= 10.2.0.4
> dbms_stats.delete_column_stats(..., col_stat_type=>'HISTOGRAM');
>
>
> 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