逐次処理のカーソルFORループは読みやすさと安全性の点で優れている一方で、行ごとのコンテキストスイッチとコミット頻度の誤用が性能劣化を招きやすい。高スループットが要求されるバッチやETLでは、BULK COLLECTとLIMITによるフェッチ制御、FORALLを使った一括DML、安定したロック戦略とコミット計画を組み合わせることで、同一のビジネス要件を桁違いに効率よく実現できる。本稿では暗黙カーソルの特性を踏まえ、明示カーソルとバルク機構を中核にした最適化手順と、実運用に耐えるテンプレートコードを提示する。
暗黙カーソルFORループの特性と限界
暗黙カーソルのFORループはOPEN/FETCH/CLOSEを自動化し、例外時のクローズ漏れも起きづらい。しかし各反復で行単位のフェッチとPL/SQL側処理が交互に発生し、SQLエンジンとの往復が支配的になる。データ件数が多い場合や後段でDMLを伴う場合は、BULK COLLECTとFORALLへの置換で往復回数を最小化するのが定石である。参照だけで完結する集計やランク付けは、分析関数でSQL一発に書き換える方が根本的に速いことも多い。
明示カーソル＋BULK COLLECT/LIMITでフェッチ回数を抑制する
フェッチを一定件数の塊で受け取り、塊ごとに処理していく構造に書き換えると、SQLエンジンへの往復を大幅に削減できる。LIMITはPGAの上限と処理の重さに合わせて調整し、ループ終端は配列件数で判定する。サンプルでは与信超過の注文だけを抽出し、検証と更新を一括で行っている。
DECLARE
-- バルク用の行型とコレクション
TYPE t_order IS RECORD(
order_id NUMBER,
customer_id NUMBER,
amount NUMBER,
limit_amt NUMBER
);
TYPE t_order_tab IS TABLE OF t_order INDEX BY PLS_INTEGER;
v_rows t_order_tab;
v_limit PLS_INTEGER := 500; -- PGAと処理時間のバランスで調整
CURSOR c_target IS
SELECT o.order_id, o.customer_id, o.amount, c.credit_limit AS limit_amt
FROM app.orders o
JOIN app.customers c ON c.customer_id = o.customer_id
WHERE o.status = 'PENDING'
AND o.amount > c.credit_limit
ORDER BY o.customer_id, o.order_id;
BEGIN
OPEN c_target;
LOOP
FETCH c_target BULK COLLECT INTO v_rows LIMIT v_limit;
EXIT WHEN v_rows.COUNT = 0;
-- 検証や補助情報の付与があればここで配列をスキャン
FOR i IN 1..v_rows.COUNT LOOP
NULL; -- 例：与信超過理由のメモ作成など
END LOOP;
-- DMLはFORALLで一括送信（SAVE EXCEPTIONSで部分失敗を後処理）
BEGIN
FORALL i IN 1..v_rows.COUNT SAVE EXCEPTIONS
UPDATE app.orders
SET status = 'ON_HOLD'
WHERE order_id = v_rows(i).order_id;
COMMIT; -- チャンク単位コミットでリカバリ容易性とロック時間の妥協点を取る
EXCEPTION
WHEN OTHERS THEN
-- 部分失敗時のログと補償は標準ロガーに委譲（例示）
ROLLBACK;
-- pkg_logger.log_error(SQLERRM);
RAISE;
END;
END LOOP;
CLOSE c_target;
END;
/
FETCH制御とPGAメモリのバランスを取る
LIMITを大きくすれば往復は減るが、配列の保持コストと処理中の一時領域が増える。計算量の大きい検証や複数の大きな配列を並行して扱う場合は、500から1000程度で様子を見て、実測の経過時間とUNDOの消費、ロック競合の発生状況で最適点を探る。フェッチした件数は配列カウントで判定し、SQL%ROWCOUNTではなく配列基準で制御すると混乱がない。長大トランザクションを避けるため、チャンクコミットの粒度もLIMITと同期させるのが実装上わかりやすい。
一括DMLでの例外収集と再実行設計
FORALLのSAVE EXCEPTIONSは失敗行のインデックスとエラーコードをSQL%BULK_EXCEPTIONSに蓄える。致命的な鍵競合を除けば、その場で全体ロールバックせず、失敗分だけをキューへ積んで後続の補償処理に回すと可用性が高い。補償側はSKIP LOCKEDや冪等なMERGEにより、再実行で重複更新が起きないように書く。
DECLARE
TYPE t_id_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_ids t_id_tab := t_id_tab(1001,1002,1003,1004);
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
DELETE FROM app.work_queue WHERE job_id = v_ids(i);
EXCEPTION
WHEN dml_errors THEN
FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
-- pkg_logger.log_error('idx='||SQL%BULK_EXCEPTIONS(j).ERROR_INDEX||
-- ' code='||SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
NULL;
END LOOP;
END;
/
並列ワーカーでのフェッチとロックの安定化
複数ワーカーが同一の未処理行を取り合うと競合が発生するため、SELECT … FOR UPDATE SKIP LOCKEDで行ロックしながら小刻みに取得すると衝突に強い。コミットはチャンクの終端に限定し、NOWAITを避けてSKIP LOCKEDで待機を伴わない取得に切り替えるとキュー処理のスループットが向上する。
DECLARE
TYPE t_job IS TABLE OF app.jobs%ROWTYPE;
v_jobs t_job;
v_limit PLS_INTEGER := 200;
BEGIN
LOOP
SELECT /*+ ROW_NUMBER_ORDERED */ *
BULK COLLECT INTO v_jobs
FROM app.jobs
WHERE status = 'READY'
FOR UPDATE SKIP LOCKED
FETCH FIRST v_limit ROWS ONLY;
EXIT WHEN v_jobs.COUNT = 0;
FORALL i IN 1..v_jobs.COUNT
UPDATE app.jobs
SET status = 'RUNNING'
WHERE CURRENT OF /* 暗黙のROWIDがないためキー指定に置換する場合あり */
(SELECT j.rowid FROM app.jobs j WHERE j.job_id = v_jobs(i).job_id);
COMMIT;
-- 実処理
FOR i IN 1..v_jobs.COUNT LOOP
NULL; -- 仕事の実体
END LOOP;
FORALL i IN 1..v_jobs.COUNT
UPDATE app.jobs SET status = 'DONE' WHERE job_id = v_jobs(i).job_id;
COMMIT;
END LOOP;
END;
/
ループをSQL一発に置き換える基準と書き換え例
前後関係を参照するだけの集計、先頭や直前レコードの比較、順位付けなどは、LAG/LEADやSUM OVERでSQLに還元するとフェッチ自体が不要になる。行ごとのIF分岐が実はウィンドウ関数で表現できる場合は、まずSQL化を試みる。
-- 逐次ループの集計を分析関数に置換（連続購入日の累積金額など）
SELECT customer_id,
purchase_dt,
amount,
SUM(amount) OVER(PARTITION BY customer_id
ORDER BY purchase_dt
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_sum
FROM app.sales
WHERE purchase_dt >= DATE '2025-01-01';
PIPELINED関数でストリーム処理に切り替える
中間結果を全件メモリに保持せずに逐次吐き出したい場合はPIPELINED関数が有効で、上流はBULKで読み、下流へPIPE ROWで流し込む。外部結合やソートを下流のSQLに委ねられるので、メモリ圧迫を抑えつつ柔軟な合成が可能になる。
CREATE TYPE t_row AS OBJECT(order_id NUMBER, amount NUMBER);
/
CREATE TYPE t_row_tab IS TABLE OF t_row;
/
CREATE OR REPLACE FUNCTION f_stream_orders
RETURN t_row_tab PIPELINED
IS
TYPE t_amt_tab IS TABLE OF app.orders.amount%TYPE;
TYPE t_id_tab IS TABLE OF app.orders.order_id%TYPE;
v_ids t_id_tab;
v_amt t_amt_tab;
BEGIN
FOR r IN (SELECT order_id, amount FROM app.orders WHERE status = 'NEW') LOOP
PIPE ROW(t_row(r.order_id, r.amount));
END LOOP;
RETURN;
END;
/
-- 下流は通常のSELECTとして合成可能
SELECT * FROM TABLE(f_stream_orders()) WHERE amount > 1000;
カーソル属性とコミット戦略を一致させる
SQL%ROWCOUNTやカーソル%FOUNDは逐次処理の進捗判断に便利だが、バルク処理では配列のCOUNTを第一指標とし、コミットはチャンク終端で一回にまとめる。トランザクション境界が変更順序の意味を持つ場合は、ルールに沿ってチャンク内の順序を固定するためにORDER BYを併用し、期待通りの副作用順が担保できるかをテストで確認する。
実運用テンプレート：抽出→検証→一括更新の三相パターン
抽出は明示カーソルとBULK COLLECT/LIMITで分割し、検証は配列スキャンで実施し、更新はFORALLで一括送信する。例外はSAVE EXCEPTIONSで集計し、致命的でなければ補償キューに回す。チャンクサイズとコミット間隔は同一に保ち、ジョブ再実行時に同じチャンク境界で再開できるよう、対象の並び順を安定化する。
DECLARE
TYPE t_rec IS RECORD(id NUMBER, amt NUMBER);
TYPE t_tab IS TABLE OF t_rec INDEX BY PLS_INTEGER;
v_rows t_tab;
v_bad t_tab;
v_lim PLS_INTEGER := 1000;
CURSOR c IS
SELECT id, amt FROM app.staging WHERE status = 'READY' ORDER BY id;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO v_rows LIMIT v_lim;
EXIT WHEN v_rows.COUNT = 0;
v_bad.DELETE;
FOR i IN 1..v_rows.COUNT LOOP
IF v_rows(i).amt < 0 THEN
v_bad(v_bad.COUNT+1) := v_rows(i); -- 補償対象へ退避
END IF;
END LOOP;
BEGIN
FORALL i IN 1..v_rows.COUNT SAVE EXCEPTIONS
INSERT INTO app.fact(id, amount) VALUES (v_rows(i).id, v_rows(i).amt);
FORALL i IN 1..v_rows.COUNT
UPDATE app.staging SET status = 'DONE' WHERE id = v_rows(i).id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
-- 失敗分を補償キューへ
FOR i IN 1..v_rows.COUNT LOOP
INSERT INTO app.comp_queue(id, amount, reason) VALUES (v_rows(i).id, v_rows(i).amt, SQLERRM);
END LOOP;
COMMIT;
END;
END LOOP;
CLOSE c;
END;
/
まとめ
行単位のFORループは読みやすさの代償として往復コストを抱えやすく、BULK COLLECT/LIMITとFORALLで塊処理へ転換することが性能最適化の第一歩になる。フェッチは配列件数を基準に制御し、チャンク終端にコミットを揃えるとトランザクションの見通しが良くなる。重複や競合の懸念がある並行処理はSKIP LOCKEDで安定化し、補償は冪等なDMLと再実行前提の設計で吸収する。根本的にSQLに還元できる処理は分析関数やPIPELINEDでストリーム化し、PL/SQL側の負担を最小化する。これらの原則をテンプレートとして定着させれば、読みやすさとスループットを両立したカーソル設計が標準化できる。
