[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