[20140505]删除直方图_delete histogram.txt
昨天看了https://jonathanlewis.wordpress.com/2014/05/01/delete-histogram/
介绍删除直方图的方法,这样比较快捷.
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id1 , mod(rownum,100)+1 id2, 'test' name from dual connect by levelTable created.
SCOTT@test> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1,for columns id1 size 254 ,for columns id2 size 254');
PL/SQL procedure successfully completed.
SCOTT@test> select column_name,num_distinct,histogram from dba_tab_columns where owner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
-------------------- ------------ ---------------
ID1 10000 HEIGHT BALANCED
ID2 100 FREQUENCY
NAME 1 NONE
-- Jonathan Lewis的方法,
declare
srec dbms_stats.statrec;
m_distcnt number;
m_density number;
m_nullcnt number;
m_avgclen number;
n_array dbms_stats.numarray;
begin
dbms_stats.get_column_stats(
ownname => user,
tabname => 't',
colname => 'id1',
distcnt => m_distcnt,
density => m_density,
nullcnt => m_nullcnt,
srec => srec,
avgclen => m_avgclen
);
srec.bkvals := null;
srec.novals := dbms_stats.numarray(
utl_raw.cast_to_number(srec.minval),
utl_raw.cast_to_number(srec.maxval)
);
srec.epc := 2;
dbms_stats.prepare_column_values(srec, srec.novals);
m_density := 1/m_distcnt;
dbms_stats.set_column_stats(
ownname => user,
tabname => 't',
colname => 'id1',
distcnt => m_distcnt,
density => m_density,
nullcnt => m_nullcnt,
srec => srec,
avgclen => m_avgclen
);
exception
when others then
raise; -- should handle div/0
end;
/
SCOTT@test> select column_name,num_distinct,histogram from dba_tab_columns where owner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
-------------------- ------------ ---------------
ID1 10000 NONE
ID2 100 FREQUENCY
NAME 1 NONE
--Id1字段的直方图删除了.
--我常用的方法就是给字段修改某个属性.
SCOTT@test> execute dbms_stats.set_column_stats(ownname=>user,tabname=>'T',colname=> 'id2',distcnt=>NULL);
PL/SQL procedure successfully completed.
SCOTT@test> select column_name,num_distinct,histogram from dba_tab_columns where owner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
-------------------- ------------ ---------------
ID1 10000 NONE
ID2 100 NONE
NAME 1 NONE
--实际上11G的包dbms_stats提供了delete_column_stats可以删除统计,这样方便不少.我好像以前测试10g不行!
SCOTT@test> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1,for columns id1 size 254 ,for columns id2 size 254');
PL/SQL procedure successfully completed.
SCOTT@test> exec dbms_stats.delete_column_stats(ownname=>user, tabname=>'T', colname=>'ID1', col_stat_type=> 'HISTOGRAM');
PL/SQL procedure successfully completed.
SCOTT@test> select column_name,num_distinct,histogram from dba_tab_columns where owner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
-------------------- ------------ ---------------
ID1 10000 NONE
ID2 100 FREQUENCY
NAME 1 NONE
--如果执行如下,该字段的统计信息一起删除.
SCOTT@test> execute dbms_stats.delete_column_stats(ownname=>user,tabname=>'t',colname=>'id2');
PL/SQL procedure successfully completed.
SCOTT@test> select column_name,num_distinct,histogram from dba_tab_columns where owner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
-------------------- ------------ ---------------
ID1 10000 NONE
ID2 NONE
NAME 1 NONE