[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