【Oracle】インデックス圧縮完全ガイド｜キー圧縮（COMPRESS）・Advanced Index Compression でストレージと I/O を削減する

-- COMPRESS n: 先頭 n 列をキー圧縮する（n はインデックスの列数 -1 以下）
-- 例: (department_id, job_id, employee_id) の複合インデックスで先頭 2 列を圧縮する
CREATE INDEX emp_dept_job_idx
    ON employees(department_id, job_id, employee_id)
    COMPRESS 2;    -- department_id と job_id の繰り返しを圧縮する
-- COMPRESS だけ（n なし）= COMPRESS 1 と同義（先頭1列を圧縮）

-- 圧縮なしの場合と比較するためにもう一方のインデックスも作成する
CREATE INDEX emp_dept_job_nocomp_idx
    ON employees(department_id, job_id, employee_id)
    NOCOMPRESS;    -- 明示的に圧縮なし（デフォルト）

-- インデックス情報を確認する
SELECT
    index_name,
    compression,      -- ENABLED / DISABLED
    leaf_blocks,      -- リーフブロック数（少ないほどコンパクト）
    num_rows,
    blevel            -- B-Tree の深さ
FROM DBA_INDEXES
WHERE table_name = 'EMPLOYEES'
  AND index_name IN ('EMP_DEPT_JOB_IDX', 'EMP_DEPT_JOB_NOCOMP_IDX')
ORDER BY index_name;

-- 統計更新後に比較する
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_DEPT_JOB_IDX');
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_DEPT_JOB_NOCOMP_IDX');

-- COMPRESS ADVANCED LOW: 標準的な Advanced Index Compression（推奨）
-- ・単一列インデックスにも対応
-- ・一意インデックスにも対応
-- ・標準キー圧縮より効果が高いことが多い
-- ・DML パフォーマンスへの影響は LOW
CREATE INDEX emp_email_idx
    ON employees(email)
    COMPRESS ADVANCED LOW;

-- 一意インデックスにも適用できる（標準 COMPRESS では制限あり）
CREATE UNIQUE INDEX emp_email_uniq_idx
    ON employees(email)
    COMPRESS ADVANCED LOW;

-- COMPRESS ADVANCED HIGH: より高い圧縮率（Oracle 18c 以降、PartitionedTableなど特定ケース向け）
-- DML オーバーヘッドが HIGH より大きい
CREATE INDEX sales_date_product_idx
    ON sales(sale_date, product_id)
    COMPRESS ADVANCED HIGH;

-- Advanced Index Compression のステータスを確認する
SELECT index_name, compression
FROM DBA_INDEXES
WHERE table_name = 'EMPLOYEES';
-- COMPRESSION: 'ADVANCED LOW', 'ADVANCED HIGH', 'ENABLED'(標準), 'DISABLED'(なし)

-- 標準キー圧縮と Advanced 圧縮の使い分け
-- ・非一意複合インデックス + 先行列のカーディナリティが低い → 標準 COMPRESS n が効果的
-- ・一意インデックス or 単一列インデックス → COMPRESS ADVANCED LOW
-- ・Advanced Compression ライセンスなし → 標準 COMPRESS n のみ使用可

-- 既存インデックスを圧縮付きで再構築する（オンラインで実行可能）
ALTER INDEX hr.emp_dept_job_nocomp_idx
    REBUILD COMPRESS 2 ONLINE;
-- ONLINE: テーブルをロックせずに再構築（Enterprise Edition のみ）

-- Advanced 圧縮に切り替える
ALTER INDEX hr.emp_dept_job_idx
    REBUILD COMPRESS ADVANCED LOW ONLINE;

-- 圧縮を解除する（非圧縮に戻す）
ALTER INDEX hr.emp_dept_job_idx
    REBUILD NOCOMPRESS ONLINE;

-- 再構築後に統計を更新する
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_DEPT_JOB_IDX');

-- 変更後のリーフブロック数を確認して圧縮効果を測る
SELECT index_name, compression, leaf_blocks, num_rows
FROM DBA_INDEXES
WHERE index_name = 'EMP_DEPT_JOB_IDX';

-- ANALYZE INDEX: インデックス構造を分析する（INDEX_STATS に結果が格納される）
ANALYZE INDEX hr.emp_dept_job_idx VALIDATE STRUCTURE;

-- INDEX_STATS: 分析結果を確認する（直前の ANALYZE INDEX の結果のみ）
SELECT
    name,
    height,          -- B-Tree の高さ
    blocks,          -- 総ブロック数
    lf_rows,         -- リーフ行数
    lf_blks,         -- リーフブロック数
    lf_rows_len,     -- リーフ行の総バイト数
    br_rows,         -- ブランチ行数
    br_blks,         -- ブランチブロック数
    del_lf_rows,     -- 削除済みリーフ行数（再利用待ち）
    ROUND(del_lf_rows / lf_rows * 100, 1) AS pct_deleted  -- 削除済み率（高い場合は REBUILD が有効）
FROM INDEX_STATS
WHERE name = 'EMP_DEPT_JOB_IDX';

-- 削除済み行が多い場合は REBUILD で領域を回収する
-- pct_deleted が 20% 以上ならインデックスの再構築を検討する
ALTER INDEX hr.emp_dept_job_idx REBUILD ONLINE;

-- スキーマ内の全インデックスの圧縮状態を一覧表示する
SELECT
    i.index_name,
    i.table_name,
    i.index_type,
    i.uniqueness,
    i.compression,
    i.leaf_blocks,
    s.bytes / 1024 / 1024 AS size_mb
FROM DBA_INDEXES i
LEFT JOIN DBA_SEGMENTS s ON s.segment_name = i.index_name AND s.owner = i.owner
WHERE i.owner = 'HR'
ORDER BY s.bytes DESC NULLS LAST;