删除oracle查询直方图,[20140505]删除直方图_delete histogram

[20140505]删除直方图_delete histogram.txt

昨天看了

介绍删除直方图的方法,这样比较快捷.

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 level<=1e4;

Table 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