【Oracle】拡張統計（Extended Statistics）完全ガイド｜列グループ統計・式統計・DBMS_STATS.CREATE_EXTENDED_STATS まで解説

-- DBMS_STATS.CREATE_EXTENDED_STATS で列グループ統計を作成する
-- 戻り値: 作成された拡張統計の内部名（SYS_STU... 形式の仮想列名）
DECLARE
    v_ext_name VARCHAR2(30);
BEGIN
    -- CITY と PREFECTURE の組み合わせに列グループ統計を作成する
    v_ext_name := DBMS_STATS.CREATE_EXTENDED_STATS(
        ownname  => 'HR',
        tabname  => 'CUSTOMERS',
        extension => '(CITY, PREFECTURE)'   -- 列名をカンマ区切りで列挙
    );
    DBMS_OUTPUT.PUT_LINE('作成された拡張統計名: ' || v_ext_name);
    -- → SYS_STU#S#WF25Z#QAHIHE#MOFFMM のような内部名が返る
END;
/

-- 統計を収集する（GATHER_TABLE_STATS で拡張統計も一緒に収集される）
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname  => 'HR',
        tabname  => 'CUSTOMERS',
        cascade  => TRUE   -- インデックス統計も同時収集
    );
END;
/

-- 作成された拡張統計の一覧を確認する
SELECT
    owner,
    table_name,
    extension_name,   -- 内部名（SYS_STU... 形式）
    extension,        -- 列グループの定義（例: (CITY, PREFECTURE)）
    creator,          -- MANUAL（手動）/ AUTO-FILTER（自動）
    droppable         -- YES = DROP で削除可能
FROM DBA_STAT_EXTENSIONS
WHERE owner = 'HR'
ORDER BY table_name, extension_name;

-- 拡張統計の実際の統計値は DBA_TAB_COL_STATISTICS で確認する
-- extension_name が仮想列名として登録される
SELECT column_name, num_distinct, num_nulls, last_analyzed
FROM DBA_TAB_COL_STATISTICS
WHERE owner = 'HR'
  AND table_name = 'CUSTOMERS'
  AND column_name LIKE 'SYS_STU%'   -- 拡張統計の仮想列
ORDER BY column_name;

-- 相関のある複数列を条件にするクエリ（列グループ統計なし → 見積もり誤差が大きい）
EXPLAIN PLAN FOR
SELECT * FROM customers
WHERE city = 'Tokyo' AND prefecture = 'Tokyo';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
-- Rows 列の E-Rows（見積もり）と実際の行数を比べる

-- 列グループ統計あり → より正確な E-Rows になる
-- Note 欄に "column statistics used" は表示されないが、Rows 見積もりが改善される

-- UPPER(last_name) に対する式統計を作成する
DECLARE
    v_ext_name VARCHAR2(30);
BEGIN
    v_ext_name := DBMS_STATS.CREATE_EXTENDED_STATS(
        ownname   => 'HR',
        tabname   => 'EMPLOYEES',
        extension => '(UPPER(LAST_NAME))'   -- 式をそのまま記述する
    );
    DBMS_OUTPUT.PUT_LINE('式統計名: ' || v_ext_name);
END;
/

-- 統計を収集する
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR', tabname => 'EMPLOYEES');
END;
/

-- UPPER(last_name) を条件にしたクエリで式統計が使われる
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
-- 式統計があることで E-Rows の精度が向上する

-- ステップ①: ワークロードのモニタリングを開始する
-- 指定時間内（ここでは 3600 秒 = 1時間）に実行された SQL を収集する
BEGIN
    DBMS_STATS.SEED_COL_USAGE(
        sqlset_name  => NULL,     -- NULL = 現在実行中の SQL を直接収集
        owner_name   => NULL,
        time_limit   => 3600      -- 収集期間（秒）
    );
END;
/

-- ステップ②: 収集期間中に実際のワークロードを実行する
-- （アプリケーションを通常通り稼働させる）

-- ステップ③: 有用な拡張統計の候補を確認する
SELECT table_name, extension
FROM DBA_COL_USAGE_STATISTICS
WHERE owner = 'HR'
ORDER BY table_name, extension;

-- ステップ④: 推奨される拡張統計をまとめて作成するスクリプトを取得する
SELECT DBMS_STATS.REPORT_COL_USAGE(
    ownname => 'HR',
    tabname => 'CUSTOMERS'
) FROM DUAL;

-- ステップ⑤: 実際に拡張統計を作成する（REPORT が推奨した内容をもとに手動で実行）
DECLARE
    v_ext VARCHAR2(30);
BEGIN
    v_ext := DBMS_STATS.CREATE_EXTENDED_STATS('HR', 'CUSTOMERS', '(CITY, PREFECTURE)');
    DBMS_STATS.GATHER_TABLE_STATS('HR', 'CUSTOMERS');
    DBMS_OUTPUT.PUT_LINE(v_ext);
END;
/

-- 特定の拡張統計を削除する（extension_name または extension 定義を指定）
BEGIN
    DBMS_STATS.DROP_EXTENDED_STATS(
        ownname   => 'HR',
        tabname   => 'CUSTOMERS',
        extension => '(CITY, PREFECTURE)'   -- 作成時と同じ定義を指定
    );
END;
/

-- 削除されたか確認する
SELECT extension_name, extension
FROM DBA_STAT_EXTENSIONS
WHERE owner = 'HR' AND table_name = 'CUSTOMERS';