INSERT、UPDATE、DELETEなどのDML操作を繰り返し実行して大量のレコードを一括更新する場合、FORALL文を使ってバルク・バインドすると、単一のDML操作で複数のDML操作を一括処理することができ、パフォーマンスが向上します。
DECLARE
TYPE empno_tbl_type IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
empno_tbl empno_tbl_type;
BEGIN
SELECT EMPNO BULK COLLECT INTO empno_tbl FROM EMP;
FORALL i IN 2..4
UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = empno_tbl( i );
END;
/
この時、バルク・バインドする値は事前にコレクション変数に格納しておく必要があります。
FORALL文ではコレクション変数に格納した値のうち、添字の最小値から最大値の範囲の値をバルク・バインドします。
上記の例の場合、empno_tblに格納したempnoのうち2件目から4件目がバルク・バインドの対象となります。
また、FORALL文では以下のようにレコード型のコレクション変数を使うこともできます。（※）
DECLARE
TYPE emp_tbl_type IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tbl emp_tbl_type;
BEGIN
SELECT * BULK COLLECT INTO emp_tbl FROM EMP;
FORALL i in 2..4
INSERT INTO EMP2 VALUES emp_tbl( i );
END;
/
※レコード型のコレクション変数を使って一括INSERTするFORALL文を組み込んだプロシージャを作成した際、ALTER SYSTEM FLUSH SHARED_POOL文を実行して共有プールをクリアした後に実行すると、必ず”ORA-01461: LONG値はLONG列にのみバインドできます。”というエラーが発生するという現象に遭遇したことがあります。
その時はやむを得ずFORALL文の使用を断念しましたが、このFORALL文はまだ歴史の浅い機能なので未成熟ということなのかもしれません。
使用する際は、十分にテストをした方がよさそうです。
このFORALL文はカーソルを利用して大量のレコードを処理するようなケースで利用できればパフォーマンスの向上に有効な手段と言えそうです。
ただし、上記のような単純な例の場合は、副問い合わせを使うなどすれば単一のDMLでの操作も可能であり、当然そうした方がさらにパフォーマンスは向上します。
また、FORALL文には、コレクション変数の索引番号が添字の範囲内で連続している必要があるなど、いろいろと制限事項もあります。
故に、実際に利用できるのは非常に限られたケースだけかもしれません。
【関連エントリ】
[Oracle] BULK COLLECT句を使ったバルクバインドによる検索処理の高速化 2005/05/20
[Oracle] FORALL文を使ったバルクバインドによる更新処理の高速化 2005/05/25