【Oracle】非表示インデックス（Invisible Index）完全ガイド｜インデックスの効果テスト・廃止候補の安全な無効化まで解説

-- 非表示インデックスを作成する（オプティマイザには見えない）
CREATE INDEX emp_dept_ix ON employees(department_id) INVISIBLE;

-- 関数ベースの非表示インデックスも同様に作成できる
CREATE INDEX emp_upper_name_ix ON employees(UPPER(last_name)) INVISIBLE;

-- 既存のインデックスを非表示に変更する（DROP 不要）
ALTER INDEX emp_salary_ix INVISIBLE;

-- 非表示インデックスを可視化する（オプティマイザから見えるようにする）
ALTER INDEX emp_salary_ix VISIBLE;

-- インデックスの表示状態を確認する
SELECT index_name, table_name, visibility, status, num_rows
FROM DBA_INDEXES
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name;
-- VISIBILITY: VISIBLE（通常）/ INVISIBLE（非表示）

-- セッションレベルで非表示インデックスを有効化する
ALTER SESSION SET use_invisible_indexes = TRUE;

-- 非表示インデックスが使われるか実行計画を確認する
EXPLAIN PLAN FOR
SELECT employee_id, last_name FROM employees WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
-- インデックス利用時: INDEX RANGE SCAN が表示される
-- USE_INVISIBLE_INDEXES=FALSE の場合: TABLE ACCESS FULL になる可能性がある

-- セッションを通常設定に戻す
ALTER SESSION SET use_invisible_indexes = FALSE;

-- 現在の設定を確認する
SHOW PARAMETER use_invisible_indexes;

-- USE_INVISIBLE_INDEXES をセッション全体に有効化せず、
-- 特定の SQL だけ非表示インデックスを使う場合は INDEX ヒントを使う
SELECT /*+ INDEX(e emp_dept_ix) */
    employee_id, department_id
FROM employees e
WHERE department_id = 50;
-- INDEX ヒントで指定したインデックスは INVISIBLE でも使用される

-- インデックスの使用状況モニタリングを開始する
ALTER INDEX emp_salary_ix MONITORING USAGE;

-- アプリケーションを一定期間（数日～数週間）稼働させてから確認する
SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
FROM V$OBJECT_USAGE
WHERE index_name = 'EMP_SALARY_IX';
-- USED = YES → 期間中に1回以上使われた
-- USED = NO  → 期間中に使われなかった（廃止候補）

-- モニタリングを停止する
ALTER INDEX emp_salary_ix NOMONITORING USAGE;

-- ステップ1: 廃止候補インデックスを非表示にする（削除しない）
ALTER INDEX emp_salary_ix INVISIBLE;

-- ステップ2: 一定期間（1～2週間）本番稼働させてパフォーマンスを監視する
-- → 問題が発生した場合は ALTER INDEX emp_salary_ix VISIBLE; で即座に戻せる

-- ステップ3: 問題がなければインデックスを削除する
DROP INDEX emp_salary_ix;

-- ステップ4: 念のためインデックス削除後の実行計画を確認する
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));

-- システムレベルで全セッションに非表示インデックスを有効化する
-- 通常は推奨しない（新機能テスト・緊急対応時のみ）
ALTER SYSTEM SET use_invisible_indexes = TRUE SCOPE=MEMORY;

-- 確認後は FALSE に戻す
ALTER SYSTEM SET use_invisible_indexes = FALSE SCOPE=MEMORY;
-- SCOPE=MEMORY のみ（再起動後は元に戻る）