取得済列統計を再取得するSQLの作成

現在の列統計を再現するSQLを出力する

SQLのチューニングをする際たまに使っているのがこれです。現在取得されている列統計を再現するためのDBMS_STATS.GATHER_TABLE_STATS()実行SQLを出力します。現在取得されている統計情報を削除し、後に同じ構成で再作成する時や、別環境で同様な列統計取得状況を再現する際に使用しています。

DBMS_STATS.GATHER_TABLE_STATS()実行用SQLを出力

以下はSOONIスキーマが保持するテーブルの内、現在ヒストグラムが取得されているカラムを対象に「カラム指定で列統計を取得する」ためのDBMS_STATS.GATHER_TABLE_STATS()を作成しています。

select a.OWNER,a.TABLE_NAME
--,a.TARGET_COLUMN
,'exec DBMS_STATS.GATHER_TABLE_STATS(ownname => '''||a.OWNER||''',tabname => '''||a.TABLE_NAME||''',cascade => FALSE,method_opt => ''FOR COLUMNS '||a.TARGET_COLUMN||''');' gatcolhis
from 
(
    select w.OWNER,w.TABLE_NAME
    ,LISTAGG(w.COLUMN_NAME ,',') within group (order by  w.COLUMN_NAME) target_column
    from
    (
        select t.OWNER,t.TABLE_NAME,t.COLUMN_NAME,t.HISTOGRAM,t.LAST_ANALYZED
        from DBA_TAB_COL_STATISTICS  t
        where  t.HISTOGRAM <> 'NONE'
        and t.OWNER='SOONI'
    ) w
    group by w.OWNER,w.TABLE_NAME
) a
order by a.OWNER,a.TABLE_NAME
;

出力例

以下横長で見辛いとおもうので「Open code in new window」で表示してみてくだい。GATCOLHIS列に出力されるSQLを実行する事で、列指定の列統計を取得する事ができます。

   OWNER              TABLE_NAME                                                                                                                                                                      GATCOLHIS
________ _______________________ ______________________________________________________________________________________________________________________________________________________________________________
SOONI    CABLE                   exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'CABLE',cascade => FALSE,method_opt => 'FOR COLUMNS TEST,TEST_ITEM');
SOONI    CHAINED_ROWS            exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'CHAINED_ROWS',cascade => FALSE,method_opt => 'FOR COLUMNS TABLE_NAME');
SOONI    EX005                   exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'EX005',cascade => FALSE,method_opt => 'FOR COLUMNS C2,C3,C5');
SOONI    EX007                   exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'EX007',cascade => FALSE,method_opt => 'FOR COLUMNS C1');
SOONI    EX01                    exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'EX01',cascade => FALSE,method_opt => 'FOR COLUMNS SNAME');
SOONI    EX03                    exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'EX03',cascade => FALSE,method_opt => 'FOR COLUMNS ITEM_ID');
SOONI    KOKUDO_ADDRESS_CHOME    exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'KOKUDO_ADDRESS_CHOME',cascade => FALSE,method_opt => 'FOR COLUMNS CITY_CODE,OAZA_TOWN_CHOME_CODE');
SOONI    KOKUDO_ADDRESS_GAIKU    exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'KOKUDO_ADDRESS_GAIKU',cascade => FALSE,method_opt => 'FOR COLUMNS CITY_NAME,OAZA_TOWN_CHOME_NAME');
SOONI    MST_ADDRESS             exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'MST_ADDRESS',cascade => FALSE,method_opt => 'FOR COLUMNS PREFECTURES_CODE');
SOONI    OY_PREFECTURE           exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'OY_PREFECTURE',cascade => FALSE,method_opt => 'FOR COLUMNS REGIN_ID');
SOONI    TAB_INF                 exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'TAB_INF',cascade => FALSE,method_opt => 'FOR COLUMNS COLUMN_POSITION,INDEX_NAME,TABLE_NAME,TABLE_OWNER');
SOONI    TBL15                   exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'TBL15',cascade => FALSE,method_opt => 'FOR COLUMNS COL3');
SOONI    TBL37                   exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',tabname => 'TBL37',cascade => FALSE,method_opt => 'FOR COLUMNS COL4');

13行が選択されました。

SQL>