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@xxxxxxxxxxx>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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > 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. > -- > //www.freelists.org/webpage/oracle-l > > >