【Oracle】グローバル一時表（GTT）完全ガイド｜ON COMMIT DELETE/PRESERVE ROWS・セッション分離・インデックス・統計・WITH句との使い分け・実務パターン

-- ON COMMIT DELETE ROWS: COMMIT または ROLLBACK でデータが削除される
-- ストアドプロシージャ内の一時計算結果を保持し、プロシージャ終了と同時に消したい場合に使う
CREATE GLOBAL TEMPORARY TABLE gtt_calc_work (
    row_id     NUMBER,
    base_val   NUMBER,
    result_val NUMBER
) ON COMMIT DELETE ROWS;   -- デフォルト。省略可

-- 使い方:
BEGIN
    -- トランザクション内でデータを INSERT
    INSERT INTO gtt_calc_work VALUES (1, 100, NULL);
    INSERT INTO gtt_calc_work VALUES (2, 200, NULL);

    -- 計算処理
    UPDATE gtt_calc_work SET result_val = base_val * 1.1;

    -- 最終結果を別テーブルに反映
    INSERT INTO final_results SELECT result_val FROM gtt_calc_work;
    COMMIT;   -- ← ここで gtt_calc_work のデータが自動削除される

    -- COMMIT 後、gtt_calc_work は空
    SELECT COUNT(*) FROM gtt_calc_work;   -- 0件
END;
/

-- ON COMMIT PRESERVE ROWS: セッションが続く限りデータが保持される
-- 複数のトランザクションにまたがる中間データを蓄積したい場合に使う
CREATE GLOBAL TEMPORARY TABLE gtt_session_work (
    step_no    NUMBER,
    step_name  VARCHAR2(100),
    processed  NUMBER DEFAULT 0
) ON COMMIT PRESERVE ROWS;

-- セッション内で複数の COMMIT をまたいでデータが残る
INSERT INTO gtt_session_work VALUES (1, 'データ取込', 0);
COMMIT;   -- ← データは削除されない

INSERT INTO gtt_session_work VALUES (2, '変換処理', 0);
COMMIT;   -- ← データは削除されない

SELECT COUNT(*) FROM gtt_session_work;   -- 2件（保持されている）

-- セッションを切断した瞬間にデータが全削除される（次回接続時は0件）

-- セッション A が INSERT
-- セッション A:
INSERT INTO gtt_session_work VALUES (99, 'セッションAのデータ', 0);
SELECT COUNT(*) FROM gtt_session_work;   -- 1件（セッションAには見える）

-- セッション B（同時接続）:
SELECT COUNT(*) FROM gtt_session_work;   -- 0件（セッションBには見えない）
-- → GTT は行レベルのロックも発生しない（データが独立しているため）

-- 実務上の利点:
-- バッチ処理を複数セッションで並列実行しても、それぞれが独立した作業領域を持てる

-- GTT にも通常のテーブルと同様にインデックスを作成できる
-- インデックスの定義は共有されるが、インデックスエントリ（データ）はセッション分離される
CREATE INDEX idx_gtt_work_row ON gtt_session_work (step_no);
CREATE INDEX idx_gtt_work_step ON gtt_session_work (step_name, processed);

-- インデックスはデータと同様に COMMIT または セッション終了で自動削除される
-- → 毎回 CREATE INDEX は不要。一度作ればテーブル定義と共に永続化される

-- GTT はデータがセッションごとに変わるため、GATHER_TABLE_STATS が困難
-- DBMS_STATS.SET_TABLE_STATS で行数・ブロック数を手動設定するのが実務的

BEGIN
    DBMS_STATS.SET_TABLE_STATS(
        ownname   => USER,
        tabname   => 'GTT_SESSION_WORK',
        numrows   => 10000,   -- 予想される行数
        numblks   => 100      -- 予想ブロック数
    );
END;
/
-- → オプティマイザが GTT の行数を正しく見積もれるようになる
-- ※ Oracle 12c 以降では SESSION スコープの統計（セッションごとに自動収集）も利用可能

-- Oracle 12c 以降: GTT の統計をセッション単位で自動収集する設定
-- （GLOBAL_TEMP_TABLE_STATS を SESSION に設定すると、セッション内のデータに基づいて統計が更新される）
BEGIN
    DBMS_STATS.SET_TABLE_PREFS(
        ownname  => USER,
        tabname  => 'GTT_SESSION_WORK',
        pname    => 'GLOBAL_TEMP_TABLE_STATS',
        pval     => 'SESSION'   -- SESSION（デフォルト）または SHARED を指定
    );
END;
/

-- TRUNCATE: 自セッションのデータのみ削除（他セッションのデータは影響なし）
TRUNCATE TABLE gtt_session_work;

-- DROP TABLE: テーブル定義ごと削除（全セッションのデータが失われる）
-- 他セッションが使用中でも DROP は実行できる
-- → 実行中のバッチがある場合は DROP しないよう注意
DROP TABLE gtt_session_work;

-- ALTER TABLE: 列追加などのスキーマ変更は通常テーブルと同様に可能
-- ただし他セッションがデータを持っている状態での ALTER は影響を与える可能性がある
ALTER TABLE gtt_calc_work ADD (calc_status VARCHAR2(20) DEFAULT 'PENDING');

-- ステップ1：受注データ受け取り用 GTT（ON COMMIT PRESERVE ROWS でバッチ全体で保持）
CREATE GLOBAL TEMPORARY TABLE gtt_incoming_orders (
    order_id     NUMBER,
    customer_id  NUMBER,
    amount       NUMBER,
    status       VARCHAR2(20),
    err_msg      VARCHAR2(500)
) ON COMMIT PRESERVE ROWS;

-- ステップ2：バッチプロシージャ内での利用
CREATE OR REPLACE PROCEDURE batch_process_orders AS
    v_err_count NUMBER := 0;
BEGIN
    -- 外部表からデータを GTT に取り込む
    INSERT INTO gtt_incoming_orders (order_id, customer_id, amount, status)
    SELECT order_id, customer_id, amount, 'PENDING'
    FROM ext_orders_staging;   -- 外部ファイルから読み込んだデータ
    COMMIT;

    -- バリデーション：金額が負のものをエラーに
    UPDATE gtt_incoming_orders
    SET status  = 'ERROR',
        err_msg = '金額が0以下です'
    WHERE amount <= 0;
    COMMIT;

    -- 正常データのみ本テーブルに登録
    INSERT INTO orders (order_id, customer_id, amount)
    SELECT order_id, customer_id, amount
    FROM gtt_incoming_orders
    WHERE status = 'PENDING';

    -- エラー件数確認
    SELECT COUNT(*) INTO v_err_count
    FROM gtt_incoming_orders WHERE status = 'ERROR';

    COMMIT;

    IF v_err_count > 0 THEN
        -- エラー明細を永続テーブルにコピー（GTT はセッション終了で消えるため）
        INSERT INTO order_errors
        SELECT order_id, err_msg, SYSDATE FROM gtt_incoming_orders WHERE status = 'ERROR';
        COMMIT;
    END IF;

    DBMS_OUTPUT.PUT_LINE('処理完了: エラー' || v_err_count || '件');
    -- セッション終了時に gtt_incoming_orders は自動削除される
END;
/