【Oracle】不要なインデックスを見つけて削除する方法｜V$OBJECT_USAGE・DBA_INDEX_USAGE・重複検出・INVISIBLE化・安全な削除フローまで解説

-- 特定のインデックスの監視を開始
ALTER INDEX idx_emp_dept MONITORING USAGE;

-- テーブルの全インデックスの監視を一括開始
BEGIN
    FOR rec IN (
        SELECT index_name FROM user_indexes WHERE table_name = 'EMPLOYEES'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name || ' MONITORING USAGE';
    END LOOP;
END;
/

-- 監視結果を確認
SELECT index_name, table_name,
       monitoring,     -- YES = 監視中
       used,           -- YES = 使用された / NO = 未使用
       start_monitoring,
       end_monitoring
FROM v$object_usage
ORDER BY used, index_name;

-- USED = NO のインデックスが「監視期間中に一度も使われなかった」候補

-- 監視を停止
ALTER INDEX idx_emp_dept NOMONITORING USAGE;

-- 全インデックスの使用状況（使用回数と最終使用日時）
SELECT
    o.object_name AS index_name,
    i.table_name,
    u.total_access_count AS access_count,
    u.total_exec_count AS exec_count,
    u.total_rows_returned AS rows_returned,
    u.last_used
FROM dba_index_usage u
JOIN dba_objects o ON u.object_id = o.object_id
JOIN dba_indexes i ON o.object_name = i.index_name AND o.owner = i.owner
WHERE i.owner = USER
ORDER BY u.total_access_count;

-- 最終使用日が 30 日以上前、または一度も使われていないインデックス
SELECT o.object_name AS index_name,
       i.table_name,
       NVL(u.total_access_count, 0) AS access_count,
       u.last_used
FROM dba_indexes i
JOIN dba_objects o ON i.index_name = o.object_name
                  AND i.owner = o.owner
                  AND o.object_type = 'INDEX'
LEFT JOIN dba_index_usage u ON o.object_id = u.object_id
WHERE i.owner = USER
  AND i.index_type IN ('NORMAL', 'BITMAP', 'FUNCTION-BASED NORMAL')
  AND (u.last_used IS NULL OR u.last_used < SYSDATE - 30)
ORDER BY NVL(u.total_access_count, 0);

-- 列の組み合わせが完全に同じインデックスのペアを検出
SELECT a.index_name AS index_a,
       b.index_name AS index_b,
       a.table_name,
       a.cols
FROM (
    SELECT index_name, table_name,
           LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS cols
    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
    FROM user_ind_columns
    GROUP BY index_name, table_name
) b ON a.table_name = b.table_name AND a.cols = b.cols AND a.index_name < b.index_name;

-- idx_a(A, B) と idx_b(A, B, C) がある場合、idx_a は冗長
-- idx_b だけで idx_a のカバー範囲を包含できる
SELECT a.index_name AS redundant_index,
       b.index_name AS covering_index,
       a.table_name,
       a.cols AS redundant_cols,
       b.cols AS covering_cols
FROM (
    SELECT index_name, table_name,
           LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS cols
    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
    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 b.cols LIKE a.cols || ',%'  -- b の先頭列が a と同じ
ORDER BY a.table_name, a.index_name;

-- インデックスのサイズを確認（削除による空き容量の見積もり）
SELECT index_name, table_name,
       ROUND(bytes/1024/1024, 1) AS size_mb
FROM user_segments
WHERE segment_type = 'INDEX'
ORDER BY bytes DESC;

-- テーブルごとのインデックス合計サイズ
SELECT i.table_name,
       COUNT(*) AS index_count,
       ROUND(SUM(s.bytes)/1024/1024, 1) AS total_index_mb
FROM user_indexes i
JOIN user_segments s ON i.index_name = s.segment_name AND s.segment_type = 'INDEX'
GROUP BY i.table_name
ORDER BY total_index_mb DESC;

-- (1) インデックスを INVISIBLE にする（オプティマイザが無視する）
ALTER INDEX idx_emp_dept INVISIBLE;

-- (2) 1〜2 週間、通常の業務を実行して影響を観察
-- パフォーマンスの悪化がないか監視
-- AWR レポートや V$SQL で実行計画の変化を確認

-- (3a) 問題なし → 削除
DROP INDEX idx_emp_dept;

-- (3b) 問題あり → VISIBLE に戻す（即座に復旧）
ALTER INDEX idx_emp_dept VISIBLE;

-- 削除前にインデックスの DDL を保存
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, USER) AS ddl
FROM user_indexes
WHERE index_name = 'IDX_EMP_DEPT';

-- 結果を SQL ファイルに保存しておく
-- 万が一再作成が必要になった場合にそのまま実行できる

-- PRIMARY KEY / UNIQUE 制約のインデックスを確認
SELECT c.constraint_name, c.constraint_type, c.index_name, c.table_name
FROM user_constraints c
WHERE c.index_name IS NOT NULL
ORDER BY c.table_name;

-- これらのインデックスは DROP INDEX ではなく
-- ALTER TABLE ... DROP CONSTRAINT で制約ごと削除する

-- 作成から 60 日以上経過し、一度も使われていないインデックス
SELECT i.index_name, i.table_name,
       ROUND(s.bytes/1024/1024, 1) AS size_mb,
       o.created
FROM user_indexes i
JOIN user_objects o ON i.index_name = o.object_name AND o.object_type = 'INDEX'
JOIN user_segments s ON i.index_name = s.segment_name AND s.segment_type = 'INDEX'
LEFT JOIN dba_index_usage u
    ON o.object_id = u.object_id
WHERE i.index_type IN ('NORMAL', 'BITMAP', 'FUNCTION-BASED NORMAL')
  AND o.created < SYSDATE - 60
  AND NVL(u.total_access_count, 0) = 0
  AND i.index_name NOT IN (
      SELECT index_name FROM user_constraints WHERE index_name IS NOT NULL
  )
ORDER BY s.bytes DESC;

-- インデックスが 5 個以上あるテーブル（見直し候補）
SELECT table_name, COUNT(*) AS index_count
FROM user_indexes
GROUP BY table_name
HAVING COUNT(*) >= 5
ORDER BY index_count DESC;

-- 未使用インデックスを一括で INVISIBLE にする
BEGIN
    FOR rec IN (
        SELECT i.index_name
        FROM user_indexes i
        LEFT JOIN dba_index_usage u
            ON i.index_name = (SELECT object_name FROM dba_objects WHERE object_id = u.object_id)
        WHERE NVL(u.total_access_count, 0) = 0
          AND i.index_name NOT IN (
              SELECT index_name FROM user_constraints WHERE index_name IS NOT NULL
          )
          AND i.visibility = 'VISIBLE'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name || ' INVISIBLE';
        DBMS_OUTPUT.PUT_LINE('INVISIBLE: ' || rec.index_name);
    END LOOP;
END;
/