【Oracle】インデックスの作成・再構築・削除完全ガイド｜種類・設計判断・断片化対策・ONLINE操作まで解説

CREATE [UNIQUE] INDEX インデックス名
    ON テーブル名 (列名1 [ASC|DESC], 列名2, ...)
    [TABLESPACE 表領域名]
    [ONLINE]
    [COMPRESS n]
    [PARALLEL n | NOPARALLEL];

-- emp テーブルの dept_id 列にインデックスを作成
CREATE INDEX idx_emp_dept
    ON emp (dept_id);

-- メールアドレスに一意インデックス
CREATE UNIQUE INDEX uq_emp_email
    ON emp (email);

-- dept_id, hire_date の複合インデックス
CREATE INDEX idx_emp_dept_hire
    ON emp (dept_id, hire_date);

-- このインデックスが使われる WHERE 条件の例:
--   WHERE dept_id = 10                      ← 先頭列のみ → OK
--   WHERE dept_id = 10 AND hire_date > ...  ← 両列 → OK
--   WHERE hire_date > ...                   ← 先頭列なし → インデックス使用不可

-- 大文字変換した name でインデックスを作成
CREATE INDEX idx_emp_upper_name
    ON emp (UPPER(emp_name));

-- このインデックスが使われる条件:
--   WHERE UPPER(emp_name) = 'YAMADA'  ← OK
--   WHERE emp_name = 'Yamada'         ← 使われない（関数なし）

-- 月次合計額（単価×数量）でインデックスを作成
CREATE INDEX idx_od_amount
    ON order_detail (unit_price * qty);

-- status 列（値の種類が少ない）にビットマップインデックス
CREATE BITMAP INDEX bmp_emp_status
    ON emp (status);

CREATE INDEX idx_emp_id_rev
    ON emp (emp_id) REVERSE;

-- 不可視インデックスを作成
CREATE INDEX idx_emp_phone INVISIBLE
    ON emp (phone);

-- セッション内でのみ不可視インデックスを使わせる
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

-- 効果確認後、可視化して本番適用
ALTER INDEX idx_emp_phone VISIBLE;

CREATE INDEX idx_emp_dept
    ON emp (dept_id)
    ONLINE;

-- 基本の REBUILD（テーブルロックあり）
ALTER INDEX idx_emp_dept REBUILD;

-- オンライン再構築（本番稼働中も DML 可能）
ALTER INDEX idx_emp_dept REBUILD ONLINE;

-- 別の表領域に移動しながら再構築
ALTER INDEX idx_emp_dept REBUILD TABLESPACE indx2;

-- リーフブロックをマージして断片化を解消（DML ロックなし）
ALTER INDEX idx_emp_dept COALESCE;

-- インデックスを無効化（UNUSABLE）
-- 大量 INSERT 前に無効化してパフォーマンスを上げるテクニック
ALTER INDEX idx_emp_dept UNUSABLE;

-- 大量 INSERT 実行後に再構築して有効化
ALTER INDEX idx_emp_dept REBUILD;

-- ステップ1：統計を収集する
ANALYZE INDEX idx_emp_dept VALIDATE STRUCTURE;

-- ステップ2：断片化率を確認する
-- del_lf_rows_len / lf_rows_len が 20〜30% 以上なら再構築を検討
SELECT
    name,
    lf_rows,
    del_lf_rows,
    ROUND(del_lf_rows / DECODE(lf_rows, 0, 1, lf_rows) * 100, 2) AS deleted_pct,  -- DECODE でゼロ除算を防止
    height,
    blocks
FROM INDEX_STATS;

DROP INDEX idx_emp_dept;

SELECT index_name, index_type, uniqueness, status
FROM USER_INDEXES
WHERE table_name = 'EMP'
ORDER BY index_name;

-- 監視開始
ALTER INDEX idx_emp_dept MONITORING USAGE;

-- 一定期間後（数日〜数週間）に使用状況を確認
SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
FROM V$OBJECT_USAGE
WHERE index_name = 'IDX_EMP_DEPT';

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

GRANT CREATE ANY INDEX TO scott;
GRANT ALTER ANY INDEX  TO scott;