【Oracle】インデックス情報を取得する方法完全ガイド｜USER_INDEXES・統計情報・重複検出・DDL取得まで解説

-- 特定テーブルのインデックス一覧（状態・統計含む）
SELECT index_name,
       index_type,
       uniqueness,
       status,
       visibility,
       num_rows,
       distinct_keys,
       clustering_factor,
       leaf_blocks,
       blevel,
       last_analyzed
FROM   user_indexes
WHERE  table_name = 'EMPLOYEES'
ORDER  BY index_name;

-- DBA が特定スキーマのインデックスを確認
SELECT owner,
       index_name,
       table_name,
       index_type,
       uniqueness,
       status,
       visibility,
       num_rows,
       last_analyzed
FROM   dba_indexes
WHERE  owner = 'HR'
ORDER  BY table_name, index_name;

-- CLUSTERING_FACTOR / NUM_ROWS が 1 に近いほど効率が良い
SELECT index_name,
       table_name,
       num_rows,
       distinct_keys,
       clustering_factor,
       leaf_blocks,
       ROUND(clustering_factor / NULLIF(num_rows, 0), 4) AS cf_ratio,
       ROUND(distinct_keys / NULLIF(num_rows, 0), 4)     AS selectivity
FROM   user_indexes
WHERE  table_name = 'ORDERS'
ORDER  BY cf_ratio;

-- テーブルのインデックスと構成列を COLUMN_POSITION 順に表示
SELECT i.index_name,
       i.uniqueness,
       i.index_type,
       c.column_position,
       c.column_name,
       c.descend
FROM   user_indexes    i
       JOIN user_ind_columns c ON i.index_name = c.index_name
WHERE  i.table_name = 'EMPLOYEES'
ORDER  BY i.index_name, c.column_position;

-- 複合インデックスの列を文字列で結合して一覧化（確認しやすい形式）
SELECT index_name,
       table_name,
       LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_position) AS columns,
       COUNT(*) AS col_count
FROM   user_ind_columns
GROUP  BY index_name, table_name
ORDER  BY table_name, index_name;

-- FBI の式を確認（USER_IND_COLUMNS と結合）
SELECT c.index_name,
       c.column_position,
       CASE
           WHEN e.column_expression IS NOT NULL THEN TO_CHAR(e.column_expression)
           ELSE c.column_name
       END AS actual_column
FROM   user_ind_columns    c
       LEFT JOIN user_ind_expressions e
           ON c.index_name     = e.index_name
          AND c.column_position = e.column_position
WHERE  c.table_name = 'EMPLOYEES'
ORDER  BY c.index_name, c.column_position;

-- Step 1: 監視を開始する
ALTER INDEX idx_emp_email MONITORING USAGE;

-- Step 2: 一定期間（例：1 週間以上）通常の負荷をかける

-- Step 3: 使用状況を確認する
SELECT index_name,
       table_name,
       monitoring,     -- YES / NO（現在監視中かどうか）
       used,           -- YES / NO（監視期間中に使われたかどうか）
       start_monitoring,
       end_monitoring
FROM   v$object_usage
ORDER  BY table_name, index_name;

-- Step 4: 監視を停止する
ALTER INDEX idx_emp_email NOMONITORING USAGE;

-- インデックスの詳細ブロック統計を取得（実行後 INDEX_STATS に結果が入る）
ANALYZE INDEX idx_emp_dept VALIDATE STRUCTURE;

-- INDEX_STATS から結果を確認
SELECT name,
       height,             -- B-tree の深さ
       blocks,             -- 総ブロック数（アロケート済み）
       lf_rows,            -- リーフブロックのレコード数
       lf_blks,            -- リーフブロック数
       del_lf_rows,        -- 削除済みレコード数（これが多いと断片化）
       ROUND(del_lf_rows / NULLIF(lf_rows + del_lf_rows, 0) * 100, 2) AS del_ratio_pct,
       br_rows,            -- ブランチブロックのレコード数
       br_blks,            -- ブランチブロック数
       rows_per_key        -- キー値あたりの平均行数（低いほど選択性が高い）
FROM   index_stats;

-- 同じテーブルの同じ列構成のインデックスを検出
SELECT a.table_name,
       a.index_name  AS idx1,
       b.index_name  AS idx2,
       a.columns     AS col_list
FROM (
    SELECT index_name,
           table_name,
           LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS columns
    FROM   user_ind_columns
    GROUP  BY index_name, table_name
) a
JOIN (
    SELECT index_name,
           table_name,
           LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS columns
    FROM   user_ind_columns
    GROUP  BY index_name, table_name
) b
    ON  a.table_name = b.table_name
    AND a.columns    = b.columns
    AND a.index_name < b.index_name
ORDER  BY a.table_name;

-- インデックス A の先頭 N 列がインデックス B と一致する場合を検出
-- 例: IDX_1(A, B, C) と IDX_2(A, B) は IDX_2 が IDX_1 に包含されている
SELECT a.table_name,
       a.index_name  AS redundant_idx,   -- 削除候補（包含される方）
       b.index_name  AS covering_idx,    -- 包含する方（残す側）
       a.col_count   AS redundant_cols,
       b.col_count   AS covering_cols
FROM (
    SELECT index_name, table_name,
           LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS cols,
           COUNT(*) AS col_count
    FROM   user_ind_columns GROUP BY index_name, table_name
) a
JOIN (
    SELECT index_name, table_name,
           LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS cols,
           COUNT(*) AS col_count
    FROM   user_ind_columns GROUP BY index_name, table_name
) b
    ON  a.table_name   = b.table_name
    AND a.index_name  != b.index_name
    AND a.col_count    < b.col_count
    AND b.cols        LIKE a.cols || ',%'
ORDER  BY a.table_name, a.index_name;

-- INVISIBLE インデックスを一覧表示
SELECT index_name, table_name, status, visibility
FROM   user_indexes
WHERE  visibility = 'INVISIBLE'
ORDER  BY table_name;

-- INVISIBLE → VISIBLE に変更（オプティマイザが再び使えるようになる）
ALTER INDEX idx_emp_name VISIBLE;

-- VISIBLE → INVISIBLE に変更（削除前の影響確認に使う）
ALTER INDEX idx_emp_name INVISIBLE;

-- セッションレベルで INVISIBLE インデックスを有効にしてテスト
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

-- UNUSABLE なインデックスパーティションを検出（再構築が必要）
SELECT index_owner,
       index_name,
       partition_name,
       status,
       num_rows,
       last_analyzed
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE'
ORDER  BY index_owner, index_name, partition_name;

-- UNUSABLE インデックスパーティションを一括再構築
-- ALTER INDEX idx_name REBUILD PARTITION part_name;

-- 特定インデックスの DDL を取得
SELECT DBMS_METADATA.GET_DDL('INDEX', 'IDX_EMP_DEPT') AS ddl
FROM   dual;

-- スキーマ内の全インデックスの DDL を一括取得
BEGIN
    FOR idx IN (
        SELECT index_name FROM user_indexes ORDER BY index_name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('-- ' || idx.index_name);
        DBMS_OUTPUT.PUT_LINE(
            DBMS_METADATA.GET_DDL('INDEX', idx.index_name)
        );
        DBMS_OUTPUT.PUT_LINE('/');
    END LOOP;
END;
/

-- 別スキーマのインデックスの DDL を取得（DBA 権限必要）
SELECT DBMS_METADATA.GET_DDL('INDEX', 'IDX_EMP_DEPT', 'HR') AS ddl FROM dual;

-- 30日以上統計情報が更新されていないインデックス
SELECT index_name, table_name, last_analyzed,
       TRUNC(SYSDATE - last_analyzed) AS days_since_analyzed
FROM   user_indexes
WHERE  last_analyzed < SYSDATE - 30
    OR last_analyzed IS NULL   -- 一度も統計収集されていない
ORDER  BY last_analyzed NULLS FIRST;

-- STATUS = UNUSABLE のインデックスを検出（再構築が必要）
SELECT owner, index_name, table_name, status, last_analyzed
FROM   dba_indexes
WHERE  status = 'UNUSABLE'
ORDER  BY owner, table_name;

-- テーブルごとのインデックス数とリーフブロックの合計（大きい順）
SELECT table_name,
       COUNT(*)          AS index_count,
       SUM(leaf_blocks)  AS total_leaf_blocks
FROM   user_indexes
GROUP  BY table_name
ORDER  BY index_count DESC;