【Oracle】パーティションインデックス完全ガイド｜ローカル / グローバルインデックスの違いとパーティション管理まで解説

-- パーティションテーブル（例: 月別範囲パーティション）
CREATE TABLE sales_history (
    sale_id      NUMBER,
    sale_date    DATE,
    customer_id  NUMBER,
    amount       NUMBER,
    region       VARCHAR2(20)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2024_q1 VALUES LESS THAN (DATE '2024-04-01'),
    PARTITION p_2024_q2 VALUES LESS THAN (DATE '2024-07-01'),
    PARTITION p_2024_q3 VALUES LESS THAN (DATE '2024-10-01'),
    PARTITION p_2024_q4 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_max     VALUES LESS THAN (MAXVALUE)
);

-- ローカルインデックスの作成（LOCAL を付けるだけ）
-- PREFIXED: インデックスキーの先頭がパーティションキーを含む（sale_date が先頭）
CREATE INDEX sales_hist_date_idx ON sales_history (sale_date, customer_id) LOCAL;

-- NONPREFIXED: パーティションキーを先頭に含まない（customer_id のみ）
CREATE INDEX sales_hist_cust_idx ON sales_history (customer_id) LOCAL;

-- LOCAL インデックスはパーティションと同じ境界・同じ数のインデックスパーティションを持つ
-- パーティションの追加・削除でインデックスも自動的に追加・削除される

-- インデックスのパーティション状態を確認する
SELECT
    index_name,
    partition_name,
    status,       -- USABLE / UNUSABLE
    num_rows,
    last_analyzed
FROM USER_IND_PARTITIONS
WHERE index_name = 'SALES_HIST_DATE_IDX'
ORDER BY partition_position;

-- グローバル非パーティションインデックス（テーブル全体に跨る通常のインデックス）
-- UNIQUE 制約はグローバルインデックスで実装する（テーブル全体での一意性が保証できるため）
CREATE UNIQUE INDEX sales_hist_id_uk ON sales_history (sale_id);  -- UNIQUE: グローバルのみ可

-- グローバルパーティションインデックス: 独自のパーティション境界を指定できる
-- 例: テーブルは sale_date でパーティションされているが、インデックスは customer_id で分割
CREATE INDEX sales_hist_gcust_idx ON sales_history (customer_id)
GLOBAL PARTITION BY RANGE (customer_id) (
    PARTITION p_cust_1    VALUES LESS THAN (10000),
    PARTITION p_cust_2    VALUES LESS THAN (20000),
    PARTITION p_cust_max  VALUES LESS THAN (MAXVALUE)
);

-- グローバルインデックスのデメリット:
-- テーブルのパーティション操作（TRUNCATE / DROP PARTITION）後に UNUSABLE になる
-- 例: p_2024_q1 パーティションを TRUNCATE するとグローバルインデックスが UNUSABLE になる
ALTER TABLE sales_history TRUNCATE PARTITION p_2024_q1;
-- → グローバルインデックス sales_hist_id_uk が UNUSABLE 状態になる

-- UNUSABLE になったインデックスを確認する
SELECT index_name, status FROM USER_INDEXES
WHERE table_name = 'SALES_HISTORY' AND status = 'UNUSABLE';

-- UNUSABLE インデックスを再構築する
ALTER INDEX sales_hist_id_uk REBUILD;
-- パーティションインデックスの場合
ALTER INDEX sales_hist_gcust_idx REBUILD PARTITION p_cust_1;

-- UPDATE GLOBAL INDEXES: パーティション操作と同時にグローバルインデックスを更新する
-- → インデックスが UNUSABLE にならないため、操作後のインデックス再構築が不要
ALTER TABLE sales_history TRUNCATE PARTITION p_2024_q1 UPDATE GLOBAL INDEXES;
ALTER TABLE sales_history DROP    PARTITION p_2024_q1 UPDATE GLOBAL INDEXES;
ALTER TABLE sales_history EXCHANGE PARTITION p_2024_q1
    WITH TABLE sales_history_staging UPDATE GLOBAL INDEXES;

-- デメリット: UPDATE GLOBAL INDEXES を使うとパーティション操作自体に時間がかかる
-- → 削除するパーティションのデータ量に比例してインデックスのメンテナンスコストが発生
-- → 大量データのパーティションを DROP する場合は UPDATE GLOBAL INDEXES なしで
--   DROP → REBUILD の2ステップの方が速い場合がある

-- パーティション追加はグローバルインデックスに影響しない（UNUSABLE にならない）
ALTER TABLE sales_history ADD PARTITION p_2025_q1 VALUES LESS THAN (DATE '2025-04-01');
-- グローバルインデックスへの影響なし → REBUILD 不要

-- 新しいパーティションに LOCAL インデックスが USABLE かどうかを確認する
SELECT partition_name, status FROM USER_IND_PARTITIONS
WHERE index_name = 'SALES_HIST_DATE_IDX'
ORDER BY partition_position;