| Oracle® Database PL/SQL言語リファレンス 11gリリース2 (11.2) B56260-09 |
|
 前 |
 次 |
この章では、PL/SQLコンパイラでコードがどのように最適化されるかを説明し、効率的なPL/SQLコードを記述する方法と既存のPL/SQLコードを改善する方法について説明します。
ここでのトピック
Oracle Database 10gより前のPL/SQLコンパイラは、パフォーマンスの向上のための変更を適用せずに、作成されたソース・テキストをシステム・コードに変換していました。今回のリリースのPL/SQLでは、コードのパフォーマンスが向上するように再調整する機能を持つオプティマイザが使用されます。
このオプティマイザは、デフォルトで使用可能です。まれに、オプティマイザのオーバーヘッドによって、非常に大規模なアプリケーションをコンパイルするときに時間が長くかかる場合がありますが、この場合、(デフォルト値2のかわりに)コンパイル・パラメータPLSQL_OPTIMIZE_LEVEL=1を設定して、最適化レベルを低くすることができます。また、非常にまれですが、PL/SQLによって予測より早く例外が呼び出されたり、例外が1つも呼び出されなくなる場合があります。PLSQL_OPTIMIZE_LEVEL=1に設定すると、コードは再調整されません。
|
関連項目:
|
コンパイラが実行できる最適化の1つとして、サブプログラムのインライン化があります。サブプログラムのインライン化によって、サブプログラムの起動は、起動先サブプログラムのコピーに置き換えられます(起動先と起動元のサブプログラムが同じプログラム・ユニット内に存在する場合)。サブプログラムのインライン化を実行できるようにするには、PLSQL_OPTIMIZE_LEVELコンパイル・パラメータのデフォルト値(2)を受け入れるか、またはこのパラメータを3に設定します。
PLSQL_OPTIMIZE_LEVEL=2の場合、次のようにINLINEプラグマを使用して、インライン化する各サブプログラムを指定する必要があります。
PRAGMA INLINE (subprogram, 'YES')
subprogramがオーバーロードされた場合、前述のプラグマは、指定された名前を持つすべてのサブプログラムに適用されます。
PLSQL_OPTIMIZE_LEVEL=3の場合、PL/SQLコンパイラは、サブプログラムをインライン化する機会を探ります。インライン化するサブプログラムをユーザーが指定する必要はありません。ただし、(前述の構文に従って)INLINEプラグマを使用し、サブプログラムのインライン化に高い優先順位を指定することができ、その後、他の考慮事項または制限のためインライン化が望ましくない場合を除き、コンパイラによってサブプログラムがインライン化されます。
特定のサブプログラムがインライン化されている場合は、ほぼ常にパフォーマンスが向上します。ただし、コンパイラは最適化プロセスの初期の段階でサブプログラムをインライン化するため、サブプログラムのインライン化によって、後の段階でのより強力な最適化が妨げられる可能性があります。
サブプログラムのインライン化によって特定のPL/SQLプログラムのパフォーマンスが低下する場合は、PL/SQL階層型プロファイラ(『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照)を使用して、インライン化を無効にするサブプログラムを識別します。サブプログラムのインライン化を無効にするには、INLINEプラグマを次のように使用します。
PRAGMA INLINE (subprogram, 'NO')
INLINEプラグマは、直後にある宣言または文および一部の種類の文にのみ影響します。
INLINEプラグマが宣言の直前にある場合は、次のものに影響します。
その宣言に指定されたサブプログラムのすべての起動
その宣言内のすべての初期化値(レコードのデフォルトの初期化値を除く)
INLINEプラグマは、次に示すいずれかの文の直前にある場合、その文に指定されたサブプログラムのすべての起動に影響します。
代入
CALL
条件付き
CASE
CONTINUE WHEN
EXECUTE IMMEDIATE
EXIT WHEN
LOOP
RETURN
INLINEプラグマは、前述のリストにない文には影響しません。
例12-1では、INLINEプラグマは、PLSQL_OPTIMIZE_LEVEL=2の場合、プロシージャの起動p1(1)およびp1(2)には影響しますが、プロシージャの起動p1(3)およびp1(4)には影響しません。
例12-1 サブプログラムのインライン化の指定
PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'YES');
x:= p1(1) + p1(2) + 17; -- These 2 invocations to p1 are inlined
...
x:= p1(3) + p1(4) + 17; -- These 2 invocations to p1 are not inlined
...
例12-2では、INLINEプラグマは、PLSQL_OPTIMIZE_LEVEL=2の場合、p2という名前の両方のファンクションに影響します。
例12-2 オーバーロードされたサブプログラムのインライン化の指定
FUNCTION p2 (p boolean) return PLS_INTEGER IS ...
FUNCTION p2 (x PLS_INTEGER) return PLS_INTEGER IS ...
...
PRAGMA INLINE(p2, 'YES');
x := p2(true) + p2(3);
...
例12-3では、INLINEプラグマは、プロシージャの起動p1(1)およびp1(2)には影響しますが、プロシージャの起動p1(3)およびp1(4)には影響しません。
例12-3 サブプログラムの非インライン化の指定
PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'NO');
x:= p1(1) + p1(2) + 17; -- These 2 invocations to p1 are not inlined
...
x:= p1(3) + p1(4) + 17; -- These 2 invocations to p1 might be inlined
...
複数のプラグマが、同じ宣言または文に影響する場合があります。各プラグマは、文に対して独自の影響を及ぼします。PRAGMA INLINE(subprogram,'YES')およびPRAGMA INLINE(identifier,'NO')に同じsubprogramが含まれている場合、'NO'は'YES'をオーバーライドします。1つのPRAGMA INLINE(subprogram,'NO')は、任意の数のPRAGMA INLINE(subprogram,'YES')をオーバーライドしますが、これらのプラグマの順序は重要ではありません。
例12-4では、2番目のINLINEプラグマは1番目と3番目のINLINEプラグマをオーバーライドします。
例12-4 PRAGMA INLINE ... 'NO'によるPRAGMA INLINE ... 'YES'のオーバーライド
PROCEDURE p1 (x PLS_INTEGER) IS ... ... PRAGMA INLINE (p1, 'YES'); PRAGMA INLINE (p1, 'NO'); PRAGMA INLINE (p1, 'YES'); x:= p1(1) + p1(2) + 17; -- These 2 invocations to p1 are not inlined ...
|
関連項目:
|
次のようなPL/SQLコードは、ほぼ確実にチューニングのメリットを得ることができます。
新しいPL/SQL言語機能を利用していない古いコード。
新しいPL/SQL言語機能の詳細は、「PL/SQLの新機能」を参照してください。
|
ヒント: 古いコードのチューニングに取りかかる前に、現在のシステムのベンチマークを取り、プログラムによって起動される古いサブプログラムをプロファイルします(「PL/SQLプログラムのプロファイルおよびトレース」を参照)。PL/SQLオプティマイザ(「PL/SQLオプティマイザ」を参照)の各種の自動最適化機能により、チューニングの実行前にパフォーマンスが改善する場合があります。 |
DBMS_SQLパッケージを使用して記述された古い動的SQL文。
コンパイル時に動的SQL文の入力変数と出力変数の数およびデータ型がわかる場合、その文をシステム固有の動的SQLで記述しなおすことができます(システム固有の動的SQLは、DBMS_SQLパッケージを使用する同等のコードより著しく高速に実行されます(特に、コードをコンパイラによって最適化できる場合))。詳細は、第7章「PL/SQLの動的SQL」を参照してください。
SQL文の処理に長時間を費やすコード。
「SQL文のチューニング」を参照してください。
問合せで起動され、数百万回実行される可能性があるファンクション。
「問合せにおけるファンクションの起動のチューニング」を参照してください。
問合せ結果のループに長時間を費やすコード。
「ループのチューニング」を参照してください。
数値計算を大量に実行するコード。
「計算集中型PL/SQLコードのチューニング」を参照してください。
PL/SQLからSQLに直接渡されるデータベース定義言語(DDL)文を発行するのではなく、PL/SQL文の処理に長時間を費やすコード。
「システム固有の実行のためのPL/SQLユニットのコンパイル」を参照してください。
ここでのトピック
PL/SQLプログラムの速度が低下する最も一般的な原因は、速度の遅いSQL文です。PL/SQLプログラムのSQL文をできるだけ効率的にするには、次の方法を使用します。
適切な索引を使用します。
詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
問合せヒントを使用して、不要な全表スキャンを回避します。
詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
DBMS_STATSパッケージのサブプログラムを使用して、すべての表について現在の統計を収集します。
詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
次のものを使用して、実行計画およびSQL文のパフォーマンスを分析します。
EXPLAIN PLAN文
詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
TKPROFユーティリティを持つSQLトレース機能
詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
バルクSQL(PL/SQLとSQL間の通信のパフォーマンス・オーバーヘッドを最小化するPL/SQL機能のセット)を使用します。
詳細は、「バルクSQLおよびバルク・バインド」を参照してください。
問合せで起動されるファンクションは、数百万回実行される可能性があります。問合せで不必要にファンクションを起動しないようにして、起動はできるだけ効率的にします。
問合せの表にファンクション索引を作成してください。CREATE INDEX文(『Oracle Database SQL言語リファレンス』を参照)には少し時間がかかる場合もありますが、各行のファンクション値がキャッシュされるため、問合せを非常に高速に実行できます。
問合せによって列をファンクションに渡す場合、問合せではその列でユーザー作成索引を使用できないため、表(非常に大きい可能性がある)の行ごとにファンクションが起動される可能性があります。ファンクションの起動数を最小限に抑えるには、ネストした問合せを使用します。内側の問合せで結果セットが少ない行数になるようにフィルタリングし、外側の問合せでそれらの行のみを対象にファンクションを起動します。
例12-5では、2つの問合せで同じ結果セットを生成しますが、2番目の問合せは1番目の問合せより効率的です。(この例では、EMPLOYEES表が非常に小さいため、回数および時間の違いはごくわずかです。非常に大きい表では、これらの違いが顕著になります。)
例12-5 ネストした問合せによるパフォーマンスの改善
DECLARE starting_time TIMESTAMP WITH TIME ZONE; ending_time TIMESTAMP WITH TIME ZONE; BEGIN -- Invokes SQRT for every row of employees table: SELECT SYSTIMESTAMP INTO starting_time FROM DUAL; FOR item IN ( SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees ORDER BY col_alias ) LOOP DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias); END LOOP; SELECT SYSTIMESTAMP INTO ending_time FROM DUAL; DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time)); -- Invokes SQRT for every distinct department_id of employees table: SELECT SYSTIMESTAMP INTO starting_time FROM DUAL; FOR item IN ( SELECT SQRT(department_id) col_alias FROM (SELECT DISTINCT department_id FROM employees) ORDER BY col_alias ) LOOP IF item.col_alias IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias); END IF; END LOOP; SELECT SYSTIMESTAMP INTO ending_time FROM DUAL; DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time)); END; /
結果:
Square root of dept. ID = 3.16227766016837933199889354443271853372 Square root of dept. ID = 4.47213595499957939281834733746255247088 Square root of dept. ID = 5.47722557505166113456969782800802133953 Square root of dept. ID = 6.32455532033675866399778708886543706744 Square root of dept. ID = 7.07106781186547524400844362104849039285 Square root of dept. ID = 7.74596669241483377035853079956479922167 Square root of dept. ID = 8.36660026534075547978172025785187489393 Square root of dept. ID = 8.94427190999915878563669467492510494176 Square root of dept. ID = 9.48683298050513799599668063329815560116 Square root of dept. ID = 10 Square root of dept. ID = 10.48808848170151546991453513679937598475 Time = +000000000 00:00:00.046000000 Square root of dept. ID = 3.16227766016837933199889354443271853372 Square root of dept. ID = 4.47213595499957939281834733746255247088 Square root of dept. ID = 5.47722557505166113456969782800802133953 Square root of dept. ID = 6.32455532033675866399778708886543706744 Square root of dept. ID = 7.07106781186547524400844362104849039285 Square root of dept. ID = 7.74596669241483377035853079956479922167 Square root of dept. ID = 8.36660026534075547978172025785187489393 Square root of dept. ID = 8.94427190999915878563669467492510494176 Square root of dept. ID = 9.48683298050513799599668063329815560116 Square root of dept. ID = 10 Square root of dept. ID = 10.48808848170151546991453513679937598475 Time = +000000000 00:00:00.000000000
サブプログラムにOUTパラメータまたはIN OUTパラメータが含まれる場合、NOCOPYヒント(「NOCOPY」を参照)を使用してそれらのパラメータを宣言することで、起動のオーバーヘッドを削減できる可能性があります。
デフォルトでは、PL/SQLは、OUTおよびIN OUTサブプログラム・パラメータを値によって渡します。サブプログラムの実行前に、PL/SQLは、各OUTおよびIN OUTパラメータを一時変数にコピーし、サブプログラムの実行中、パラメータの値はこの一時変数に保持されます。サブプログラムが正常に終了すると、PL/SQLは、一時変数の値を対応する実パラメータにコピーします。サブプログラムが未処理例外を戻して終了すると、PL/SQLは実パラメータの値を変更しません。
OUTパラメータまたはIN OUTパラメータが、コレクション、レコード、ADTのインスタンスなどの大規模なデータ構造を表す場合、それらのコピーによって実行速度が低下し、(特にADTのインスタンスで)メモリー使用量が増大します。
ADTメソッドが起動されるたびに、PL/SQLによってそのADTのすべての属性がコピーされます。メソッドが正常に終了すると、そのメソッドが属性に対して行った変更が、PL/SQLによって適用されます。メソッドが未処理例外を戻して終了すると、PL/SQLは、属性を変更しません。
プログラムで、サブプログラムが未処理例外を戻して終了したときにOUTパラメータまたはIN OUTパラメータに起動前の値を保持する必要がない場合、パラメータ宣言にNOCOPYヒントを含めます。NOCOPYヒントは、コンパイラが対応する実パラメータを値ではなく参照によって渡すように要求しますが、その保証はしません。NOCOPYの詳細は、「NOCOPY」を参照してください。ADTのメンバー・メソッドでNOCOPYを使用する方法の詳細は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください。
|
注意: サブプログラムが未処理例外を戻して終了したときに実パラメータまたはADT属性で起動前の値を確実に保持するためには、NOCOPYに依存しないでください(特定の起動について、コンパイラはこのヒントに従う場合と従わない場合があります)。かわりに、サブプログラムですべての例外が処理されるようにします。 |
例12-6では、コンパイラがdo_nothing2の起動に関してNOCOPYヒントに従うと、do_nothing2の起動がdo_nothing1の起動より高速に実行されます。
例12-6 NOCOPYサブプログラム・パラメータ
DECLARE
TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;
emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
PROCEDURE get_time (t OUT NUMBER) IS
BEGIN
t := DBMS_UTILITY.get_time;
END;
PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
BEGIN
NULL;
END;
PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
BEGIN
NULL;
END;
BEGIN
SELECT * INTO emp_tab(1)
FROM employees
WHERE employee_id = 100;
emp_tab.EXTEND(49999, 1); -- Copy element 1 into 2..50000
get_time(t1);
do_nothing1(emp_tab); -- Pass IN OUT parameter
get_time(t2);
do_nothing2(emp_tab); -- Pass IN OUT NOCOPY parameter
get_time(t3);
DBMS_OUTPUT.PUT_LINE ('Call Duration (secs)');
DBMS_OUTPUT.PUT_LINE ('--------------------');
DBMS_OUTPUT.PUT_LINE ('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));
DBMS_OUTPUT.PUT_LINE ('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);
END;
/
PL/SQLアプリケーションはループの周囲に構築されることが多いため、ループ自体とループ内のコードの両方を最適化することが重要です。
結果セットを2回以上ループしたり、結果セットのループ時に他の問合せを発行する必要がある場合、元の問合せを変更して、目的の結果を正確に取得することが可能な場合があります。複数の問合せを結合できるSQLの集合演算子を調査してください(『Oracle Database SQL言語リファレンス』を参照)。
副問合せを使用して、複数の段階でフィルタリングおよびソートを行うこともできます(「副問合せによる問合せ結果セットの処理」を参照)。
ここでの推奨事項は、特に計算集中型PL/SQLコードに適用されます(ただし、それらのコードに限定されません)。
ここでのトピック
NUMBERデータ型ファミリ(「NUMBERデータ型ファミリ」を参照)のデータ型を使用することは避けてください。これらのデータ型の内部的な表現形式は、パフォーマンスよりも移植性および任意の位取りと精度に重点を置いて設計されています。これらの型のデータに対する操作では、ライブラリ算術計算が使用されますが、PLS_INTEGER型、BINARY_FLOAT型およびBINARY_DOUBLE型のデータに対する操作では、ハードウェア算術計算が使用されます。
ローカル整変数には、PLS_INTEGERを使用します(「PLS_INTEGERおよびBINARY_INTEGERデータ型」を参照)。NULL値を持つことがなく、オーバーフロー・チェックを必要とせず、パフォーマンスを重視するコードで使用されない変数には、SIMPLE_INTEGERを使用します(「PLS_INTEGERのSIMPLE_INTEGERサブタイプ」を参照)。
浮動小数点変数には、BINARY_FLOATまたはBINARY_DOUBLEを使用します(『Oracle Database SQL言語リファレンス』を参照)。NULL値を持つことがなく、パフォーマンスを重視するコードで使用されない変数には、SIMPLE_FLOATまたはSIMPLE_DOUBLEを使用します(「BINARY_FLOATおよびBINARY_DOUBLEの追加のPL/SQLサブタイプ」を参照)。
|
注意: BINARY_FLOATおよびBINARY_DOUBLEとそれらのサブタイプは、正確さが非常に重要な財務処理コードにはあまり適していません。これらの型は、常に正確に小数値を表すわけではなく、NUMBER型とは異なり四捨五入処理が行われるためです。 |
多くのSQL数値ファンクション(『Oracle Database SQL言語リファレンス』を参照)は、BINARY_FLOATパラメータおよびBINARY_DOUBLEパラメータを受け入れるバージョンでオーバーロードされます。これらのデータ型の変数をこのようなファンクションに渡したり、このようなファンクションに式を渡すときに変換ファンクションのTO_BINARY_FLOAT(『Oracle Database SQL言語リファレンス』を参照)およびTO_BINARY_DOUBLE(『Oracle Database SQL言語リファレンス』を参照)を起動することによって、計算集中型コードをスピードアップできます。
パフォーマンスを重視するコードでは、制約付きサブタイプ(「制約付きサブタイプ」を参照)を使用することは避けてください。制約付きサブタイプの変数またはパラメータに代入を行うたびに、代入される値が制約に違反していないことを保証するために実行時に余分なチェックが必要になります。
PL/SQLは実行時に、必要に応じて異なるデータ型を暗黙的(自動的)に変換します。たとえば、PLS_INTEGER変数をNUMBER変数に代入すると、PL/SQLによって、PLS_INTEGER値がNUMBER値に変換されます(各値の内部表現は異なるため)。
可能であれば、暗黙的な変換は常に最小限に抑えてください。次に例を示します。
表の列に変数を挿入する場合、または表の列から変数に値を代入する場合、その変数に表の列と同じデータ型を指定します。
各リテラルが、割当て先の変数または記述対象の式と同じデータ型になるようにします。
SQLデータ型からPL/SQLデータ型に値を変換し、変換した値を式で使用します。
たとえば、NUMBER値をPLS_INTEGER値に変換し、そのPLS_INTEGER値を式で使用します。PLS_INTEGER演算はハードウェア算術計算を使用するため、ライブラリ算術計算を使用するNUMBER演算より処理速度が速くなります。PLS_INTEGERデータ型の詳細は、「PLS_INTEGERおよびBINARY_INTEGERデータ型」を参照してください。
あるSQLデータ型の値を別のSQLデータ型の変数に代入する前に、SQL変換ファンクションを使用してソース値をターゲット・データ型に明示的に変換します(SQL変換ファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください)。
異なるデータ型のパラメータを受け入れるバージョンでサブプログラムをオーバーロードし、そのパラメータ型の各バージョンを最適化します。オーバーロードされたサブプログラムの詳細は、「オーバーロードされたサブプログラム」を参照してください。
|
関連項目:
|
SQLには、PL/SQLコードよりも効率的な低レベルのコードを使用する高度に最適化された文字ファンクションが多くあります。PL/SQLコードを記述するかわりにこれらのファンクションを使用することで、同じ処理を実行できます。
|
参照:
|
バルクSQLは、PL/SQLとSQL間の通信のパフォーマンス・オーバーヘッドを最小化します。
PL/SQLとSQLは、次のように通信します。PL/SQLエンジンは、SELECT INTO文またはDML文を実行するために、SQLエンジンに問合せまたはDML文を送信します。SQLエンジンは、問合せまたはDML文を実行し、結果をPL/SQLエンジンに戻します。
バルクSQLを構成するPL/SQL機能は、FORALL文およびBULK COLLECT句です。FORALL文は、DML文をPL/SQLからSQLに1文ずつではなくバッチで送信します。BULK COLLECT句は、結果をSQLからPL/SQLに1つずつではなくバッチで戻します。問合せまたはDML文が4つ以上のデータベース行に影響する場合は、バルクSQLでパフォーマンスを大幅に向上できます。
|
注意: リモート表では、バルクSQLは実行できません。 |
SQL文に指定されたPL/SQL変数に値を代入することを、バインドと呼びます。PL/SQLバインド操作は、次のカテゴリに分類されます。
インバインドおよびアウトバインドでは、バルクSQLはバルク・バインドを使用します(つまり、値のコレクション全体を一度にバインドします)。n個の要素があるコレクションの場合、バルクSQLは単一の操作で、n回分のSELECT INTO文またはDML文に相当する処理を実行できます。バルクSQLを使用する問合せでは、行ごとにFETCH文を使用することなく、任意の数の行を戻すことができます。
ここでのトピック
FORALL文は、バルクSQLの機能であり、DML文をPL/SQLからSQLに1文ずつではなくバッチで送信します。FORALL文を理解するため、最初に例12-7のFOR LOOP文について考えてみます。これらのDML文は、PL/SQLからSQLに1文ずつ送信されます。
DELETE FROM employees_temp WHERE department_id = depts(10); DELETE FROM employees_temp WHERE department_id = depts(30); DELETE FROM employees_temp WHERE department_id = depts(70);
例12-7 FOR LOOP文のDELETE文
DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FOR i IN depts.FIRST..depts.LAST LOOP DELETE FROM employees_temp WHERE department_id = depts(i); END LOOP; END; /
次に、例12-8のFORALL文について考えてみます。3つの同じDML文がPL/SQLからSQLにバッチとして送信されます。
例12-8 FORALL文のDELETE文
DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST..depts.LAST DELETE FROM employees_temp WHERE department_id = depts(i); END; /
通常、FORALL文は、同等のFOR LOOP文よりはるかに高速です。ただし、FOR LOOP文には複数のDML文を含めることができますが、FORALL文に含めることができるDML文は1つのみです。FORALL文によってSQLに送信されるDML文のバッチは、VALUES句とWHERE句のみが異なります。これらの句の値は、データが移入された既存のコレクションから取得される必要があります。
|
注意: FORALL文のDML文では、複数のコレクションを参照できますが、パフォーマンス上のメリットは、FORALLの索引変数を索引として使用するコレクション参照に対してのみ適用されます。 |
例12-9では、2つのデータベース表に同じコレクション要素を挿入します(1番目の表にはFOR LOOP文を使用し、2番目の表にはFORALL文を使用して、各文の実行にかかる時間を表示します)。(時間は実行ごとに異なります。)
例12-9 FOR LOOP文およびFORALL文におけるINSERT文の時間の違い
DROP TABLE parts1;
CREATE TABLE parts1 (
pnum INTEGER,
pname VARCHAR2(15)
);
DROP TABLE parts2;
CREATE TABLE parts2 (
pnum INTEGER,
pname VARCHAR2(15)
);
DECLARE
TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
pnums NumTab;
pnames NameTab;
iterations CONSTANT PLS_INTEGER := 50000;
t1 INTEGER;
t2 INTEGER;
t3 INTEGER;
BEGIN
FOR j IN 1..iterations LOOP -- populate collections
pnums(j) := j;
pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP;
t1 := DBMS_UTILITY.get_time;
FOR i IN 1..iterations LOOP
INSERT INTO parts1 (pnum, pname)
VALUES (pnums(i), pnames(i));
END LOOP;
t2 := DBMS_UTILITY.get_time;
FORALL i IN 1..iterations
INSERT INTO parts2 (pnum, pname)
VALUES (pnums(i), pnames(i));
t3 := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
DBMS_OUTPUT.PUT_LINE('---------------------');
DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)/100));
DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR((t3 - t2)/100));
COMMIT;
END;
/
結果は次のようになります。
Execution Time (secs) --------------------- FOR LOOP: 2.16 FORALL: .11 PL/SQL procedure successfully completed.
例12-10では、FORALL文がコレクションのサブセットに適用されます。
例12-10 コレクションのサブセットに対するFORALL文
DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS VARRAY(10) OF NUMBER;
depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN
FORALL j IN 4..7
DELETE FROM employees_temp WHERE department_id = depts(j);
END;
/
ここでのトピック
FORALL文の境界句で疎コレクションを参照する場合、INDICES OF句またはVALUES OF句を使用して既存の索引値のみを指定します。INDICES OFは、文字列で索引付けされている連想配列を除く任意のコレクションに使用できます。VALUES OFは、PLS_INTEGERで索引付けされているPLS_INTEGER要素のコレクションにのみ使用できます。
PLS_INTEGERで索引付けされているPLS_INTEGER要素のコレクションには、索引コレクションを使用できます。索引コレクションは、他のコレクションの要素に対するポインタのコレクションです(索引付けされたコレクション)。
索引コレクションは、異なるFORALL文で同じコレクションの異なるサブセットを処理する場合に便利です。時間とメモリーを大量に費やす可能性のあるサブセットを表す新しいコレクションに元のコレクションの要素をコピーするかわりに、索引コレクションを使用して各サブセットを表し、各索引コレクションを異なるFORALL文のVALUES OF句で使用します。
例12-11では、INDICES OF句付きでFORALL文を使用し、表に疎コレクションの要素を入れます。その後、VALUES OF句付きで2つのFORALL文を使用し、2つの表にコレクションのサブセットを入れます。
例12-11 疎コレクションおよびそのサブセットに対するFORALL文
DROP TABLE valid_orders; CREATE TABLE valid_orders ( cust_name VARCHAR2(32), amount NUMBER(10,2) ); DROP TABLE big_orders; CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0; DROP TABLE rejected_orders; CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0; DECLARE SUBTYPE cust_name IS valid_orders.cust_name%TYPE; TYPE cust_typ IS TABLE OF cust_name; cust_tab cust_typ; -- Collection of customer names SUBTYPE order_amount IS valid_orders.amount%TYPE; TYPE amount_typ IS TABLE OF NUMBER; amount_tab amount_typ; -- Collection of order amounts TYPE index_pointer_t IS TABLE OF PLS_INTEGER; /* Collections for pointers to elements of cust_tab collection (to represent two subsets of cust_tab): */ big_order_tab index_pointer_t := index_pointer_t(); rejected_order_tab index_pointer_t := index_pointer_t(); PROCEDURE populate_data_collections IS BEGIN cust_tab := cust_typ( 'Company1','Company2','Company3','Company4','Company5' ); amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL); END; BEGIN populate_data_collections; DBMS_OUTPUT.PUT_LINE ('--- Original order data ---'); FOR i IN 1..cust_tab.LAST LOOP DBMS_OUTPUT.PUT_LINE ( 'Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i) ); END LOOP; -- Delete invalid orders: FOR i IN 1..cust_tab.LAST LOOP IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN cust_tab.delete(i); amount_tab.delete(i); END IF; END LOOP; -- cust_tab is now a sparse collection. DBMS_OUTPUT.PUT_LINE ('--- Order data with invalid orders deleted ---'); FOR i IN 1..cust_tab.LAST LOOP IF cust_tab.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE ( 'Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i) ); END IF; END LOOP; -- Using sparse collection, populate valid_orders table: FORALL i IN INDICES OF cust_tab INSERT INTO valid_orders (cust_name, amount) VALUES (cust_tab(i), amount_tab(i)); populate_data_collections; -- Restore original order data -- cust_tab is a dense collection again. /* Populate collections of pointers to elements of cust_tab collection (which represent two subsets of cust_tab): */ FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN rejected_order_tab.EXTEND; rejected_order_tab(rejected_order_tab.LAST) := i; END IF; IF amount_tab(i) > 2000 THEN big_order_tab.EXTEND; big_order_tab(big_order_tab.LAST) := i; END IF; END LOOP; /* Using each subset in a different FORALL statement, populate rejected_orders and big_orders tables: */ FORALL i IN VALUES OF rejected_order_tab INSERT INTO rejected_orders (cust_name, amount) VALUES (cust_tab(i), amount_tab(i)); FORALL i IN VALUES OF big_order_tab INSERT INTO big_orders (cust_name, amount) VALUES (cust_tab(i), amount_tab(i)); END; /
結果:
--- Original order data --- Customer #1, Company1: $5000.01 Customer #2, Company2: $0 Customer #3, Company3: $150.25 Customer #4, Company4: $4000 Customer #5, Company5: $ --- Data with invalid orders deleted --- Customer #1, Company1: $5000.01 Customer #3, Company3: $150.25 Customer #4, Company4: $4000
正しい注文詳細が格納されたかどうかの検証:
SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders ORDER BY cust_name;
結果:
Customer Valid order amount -------------------------------- ------------------ Company1 5000.01 Company3 150.25 Company4 4000 3 rows selected.
問合せ:
SELECT cust_name "Customer", amount "Big order amount" FROM big_orders ORDER BY cust_name;
結果:
Customer Big order amount -------------------------------- ---------------- Company1 5000.01 Company4 4000 2 rows selected.
問合せ:
SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders ORDER BY cust_name;
結果:
Customer Rejected order amount -------------------------------- --------------------- Company2 0 Company5 2 rows selected.
SAVE EXCEPTIONS句なしのFORALL文で、いずれかのDML文によって未処理例外が呼び出されると、PL/SQLによってFORALL文が停止され、前のDML文で行われたすべての変更がロールバックされます。
たとえば、例12-8のFORALL文は、次のDML文を(そのいずれかによって未処理例外が呼び出されないかぎり)記載された順序で実行します。
DELETE FROM employees_temp WHERE department_id = depts(10); DELETE FROM employees_temp WHERE department_id = depts(30); DELETE FROM employees_temp WHERE department_id = depts(70);
3番目の文で未処理例外が呼び出されると、PL/SQLによって、1番目と2番目の文による変更がロールバックされます。2番目の文で未処理例外が呼び出されると、PL/SQLによって、1番目の文による変更がロールバックされ、3番目の文は実行されません。
FORALL文で呼び出される例外は、次のいずれかの方法で処理できます。
各例外が呼び出された時点で(「FORALL例外の即座の処理」を参照)
FORALL文の実行が完了した後、SAVE EXCEPTIONS句を含めることによって(「FORALL文完了後のFORALL例外の処理」を参照)
FORALL文で呼び出された例外を即座に処理するには、SAVE EXCEPTIONS句を省略して適切な例外ハンドラを記述します。(例外ハンドラの詳細は、第11章「PL/SQLのエラー処理」を参照してください。)いずれかのDML文で処理済み例外が呼び出されると、PL/SQLによってその文による変更がロールバックされますが、前のDML文による変更はロールバックされません。
例12-12では、FORALL文が3つのUPDATE文を実行するように設計されています。ただし、2番目の文によって例外が呼び出されます。例外は例外ハンドラによって処理され、エラー・メッセージの表示と1番目のUPDATE文による変更のコミットが行われます。3番目のUPDATE文は実行されません。
例12-12 FORALL例外の即時処理
DROP TABLE emp_temp; CREATE TABLE emp_temp ( deptno NUMBER(2), job VARCHAR2(18) ); CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10, 20, 30); error_message VARCHAR2(100); BEGIN -- Populate table: INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk'); INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper'); INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst'); COMMIT; -- Append 9-character string to each job: FORALL j IN depts.FIRST..depts.LAST UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j); EXCEPTION WHEN OTHERS THEN error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE (error_message); COMMIT; -- Commit results of successful updates RAISE; END; /
結果:
Procedure created.
プロシージャの起動:
BEGIN p; END; /
結果:
ORA-12899: value too large for column "HR"."EMP_TEMP"."JOB" (actual: 19, maximum: 18) ORA-06512: at "HR.P", line 27 ORA-06512: at line 2 PL/SQL procedure successfully completed.
問合せ:
SELECT * FROM emp_temp;
結果:
DEPTNO JOB
---------- ------------------
10 Clerk (Senior)
20 Bookkeeper
30 Analyst
3 rows selected.
一部のDML文が失敗した場合でもFORALL文の処理を継続するには、SAVE EXCEPTIONS句を含めます。DML文が失敗しても、PL/SQLによって例外は呼び出されず、かわりに、その失敗に関する情報が保存されます。FORALL文の完了後、PL/SQLによって、FORALL文に対して1つの例外(ORA-24381)が呼び出されます。ORA-24381の例外ハンドラでは、暗黙カーソル属性SQL%BULK_EXCEPTIONSから個々のDML文の失敗に関する情報を取得できます。
SQL%BULK_EXCEPTIONSは、直前のFORALL文の実行中に失敗したDML文に関する情報の連想配列とほぼ同じです。
SQL%BULK_EXCEPTIONS.COUNTは、失敗したDML文の数です。SQL%BULK_EXCEPTIONS.COUNTが0(ゼロ)でない場合、1からSQL%BULK_EXCEPTIONS.COUNTまでの各索引値iに対して、次のようになります。
SQL%BULK_EXCEPTIONS(i).ERROR_INDEXは、失敗したDML文の数です。
SQL%BULK_EXCEPTIONS(i).ERROR_CODEは、失敗に関するOracle Databaseエラー・コードです。
たとえば、FORALL SAVE EXCEPTIONS文で100個のDML文を実行し、10番目と64番目の文がそれぞれエラー・コードORA-12899およびORA-19278を戻して失敗した場合、次のようになります。
SQL%BULK_EXCEPTIONS.COUNT = 2
SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10
SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899
SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64
SQL%BULK_EXCEPTIONS(2).ERROR_CODE = 19278
|
注意: SAVE EXCEPTIONS句なしのFORALL文によって例外が呼び出されると、SQL%BULK_EXCEPTIONS.COUNT = 1になります。 |
エラー・コードを使用して、次のようにSQLERRMファンクション(「SQLERRMファンクション」を参照)で関連するエラー・メッセージを取得できます。
SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE))
ただし、SQLERRMによって戻されるエラー・メッセージには、置換引数は含まれません(例12-12と例12-13のエラー・メッセージを比較してください)。
例12-13は、例12-12とほぼ同じですが、次の点が異なります。
FORALL文に、SAVE EXCEPTIONS句が含まれます。
例外処理部に、ORA-24381(バルク操作が例外を呼び出して保存したときにPL/SQLによって暗黙的に呼び出される内部的に定義された例外)に対応する例外ハンドラが含まれます。この例では、ORA-24381にユーザー定義の名前dml_errorsを割り当てています。
dml_errorsの例外ハンドラは、SQL%BULK_EXCEPTIONSとSQLERRM(およびいくつかのローカル変数)を使用して、エラー・メッセージと、エラーの原因となった文、コレクション項目および文字列を表示します。
例12-13 FORALL文が完了した後のFORALL例外の処理
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10, 20, 30); error_message VARCHAR2(100); bad_stmt_no PLS_INTEGER; bad_deptno emp_temp.deptno%TYPE; bad_job emp_temp.job%TYPE; dml_errors EXCEPTION; PRAGMA EXCEPTION_INIT(dml_errors, -24381); BEGIN -- Populate table: INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk'); INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper'); INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst'); COMMIT; -- Append 9-character string to each job: FORALL j IN depts.FIRST..depts.LAST SAVE EXCEPTIONS UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j); EXCEPTION WHEN dml_errors THEN FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP error_message := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); DBMS_OUTPUT.PUT_LINE (error_message); bad_stmt_no := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX; DBMS_OUTPUT.PUT_LINE('Bad statement #: ' || bad_stmt_no); bad_deptno := depts(bad_stmt_no); DBMS_OUTPUT.PUT_LINE('Bad department #: ' || bad_deptno); SELECT job INTO bad_job FROM emp_temp WHERE deptno = bad_deptno; DBMS_OUTPUT.PUT_LINE('Bad job: ' || bad_job); END LOOP; COMMIT; -- Commit results of successful updates WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unrecognized error.'); RAISE; END; /
結果:
Procedure created.
プロシージャの起動:
BEGIN p; END; /
結果:
ORA-12899: value too large for column (actual: , maximum: ) Bad statement #: 2 Bad department #: 20 Bad job: Bookkeeper PL/SQL procedure successfully completed.
問合せ:
SELECT * FROM emp_temp;
結果:
DEPTNO JOB
---------- ------------------
10 Clerk (Senior)
20 Bookkeeper
30 Analyst (Senior)
3 rows selected.
FORALL文の完了後、各DML文によって影響を受けた行の数を、暗黙カーソル属性SQL%BULK_ROWCOUNTから取得できます。(FORALL文の影響を受けた行の合計数を取得するには、「SQL%ROWCOUNT属性: 影響を受けた行数」で説明されている暗黙カーソル属性SQL%ROWCOUNTを使用します)。
SQL%BULK_ROWCOUNTは、i番目の要素が、直前に完了したFORALL文のi番目のDML文によって影響を受けた行の数に相当する連想配列とほぼ同じです。要素のデータ型はPLS_INTEGERです。
|
注意: 行の数がPLS_INTEGERの最大値を超える場合、要素には負の値が含まれます。PLS_INTEGERの詳細は、「PLS_INTEGERおよびBINARY_INTEGERデータ型」を参照してください。 |
例12-14では、SQL%BULK_ROWCOUNTを使用してFORALL文の各DELETE文で削除された行の数を表示し、SQL%ROWCOUNTを使用して削除された行の合計数を表示します。
例12-14 FORALLの各DELETEで影響を受けた行の数の表示
DROP TABLE emp_temp;
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(30, 50, 60);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp_temp WHERE department_id = depts(j);
FOR i IN depts.FIRST..depts.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
'Statement #' || i || ' deleted ' ||
SQL%BULK_ROWCOUNT(i) || ' rows.'
);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total rows deleted: ' || SQL%ROWCOUNT);
END;
/
結果:
Statement #1 deleted 6 rows. Statement #2 deleted 45 rows. Statement #3 deleted 5 rows. Total rows deleted: 56
例12-15では、SQL%BULK_ROWCOUNTを使用してFORALL文の各INSERT SELECT構文で挿入された行の数を表示し、SQL%ROWCOUNTを使用して挿入された行の合計数を表示します。
例12-15 FORALLの各INSERT SELECTで影響を受けた行の数の表示
DROP TABLE emp_by_dept; CREATE TABLE emp_by_dept AS SELECT employee_id, department_id FROM employees WHERE 1 = 0; DECLARE TYPE dept_tab IS TABLE OF departments.department_id%TYPE; deptnums dept_tab; BEGIN SELECT department_id BULK COLLECT INTO deptnums FROM departments; FORALL i IN 1..deptnums.COUNT INSERT INTO emp_by_dept (employee_id, department_id) SELECT employee_id, department_id FROM employees WHERE department_id = deptnums(i) ORDER BY department_id, employee_id; FOR i IN 1..deptnums.COUNT LOOP -- Count how many rows were inserted for each department; that is, -- how many employees are in each department. DBMS_OUTPUT.PUT_LINE ( 'Dept '||deptnums(i)||': inserted '|| SQL%BULK_ROWCOUNT(i)||' records' ); END LOOP; DBMS_OUTPUT.PUT_LINE('Total records inserted: ' || SQL%ROWCOUNT); END; /
結果:
Dept 10: inserted 1 records Dept 20: inserted 2 records Dept 30: inserted 6 records Dept 40: inserted 1 records Dept 50: inserted 45 records Dept 60: inserted 5 records Dept 70: inserted 1 records Dept 80: inserted 34 records Dept 90: inserted 3 records Dept 100: inserted 6 records Dept 110: inserted 2 records Dept 120: inserted 0 records Dept 130: inserted 0 records Dept 140: inserted 0 records Dept 150: inserted 0 records Dept 160: inserted 0 records Dept 170: inserted 0 records Dept 180: inserted 0 records Dept 190: inserted 0 records Dept 200: inserted 0 records Dept 210: inserted 0 records Dept 220: inserted 0 records Dept 230: inserted 0 records Dept 240: inserted 0 records Dept 250: inserted 0 records Dept 260: inserted 0 records Dept 270: inserted 0 records Dept 280: inserted 0 records Total records inserted: 106
BULK COLLECT句は、バルクSQLの機能であり、結果をSQLからPL/SQLに1つずつではなくバッチで戻します。BULK COLLECT句は、次の場所に使用できます。
SELECT INTO文
FETCH文
次の文のRETURNING INTO句:
DELETE文
INSERT文
UPDATE文
EXECUTE IMMEDIATE文
BULK COLLECT句を使用して、前述の各文で結果セット全体を取り出し、それを単一の操作で1つ以上のコレクション変数に格納します(一度に1つの結果行を取り出すループ文を使用するより効率的な操作です)。
|
注意: PL/SQLは、LOOP文の内側でFETCH文を処理する場合と同様の方法で、BULK COLLECT句を処理します。PL/SQLは、BULK COLLECT句付きの文が行を戻さない場合でも、例外を呼び出しません。例12-22に示すように、ターゲット・コレクションが空かどうかを確認する必要があります。 |
ここでのトピック
BULK COLLECT句付きのSELECT INTO文(SELECT BULK COLLECT INTO文とも呼ばれます)は、結果セット全体を選択して1つ以上のコレクション変数に入れます。詳細は、「SELECT INTO文」を参照してください。
|
注意: SELECT BULK COLLECT INTO文は、エイリアシングに対して不安定であるため、予期しない結果が発生する可能性があります。詳細は、「SELECT BULK COLLECT INTO文およびエイリアシング」を参照してください。 |
例12-16では、SELECT BULK COLLECT INTO文を使用して、データベースの2つの列を選択して2つのコレクション(ネストした表)に入れています。
例12-16 2つのネストした表に対するデータベースの2つの列のバルク選択
DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab;
names NameTab;
PROCEDURE print_first_n (n POSITIVE) IS
BEGIN
IF enums.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('Collections are empty.');
ELSE
DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');
FOR i IN 1 .. n LOOP
DBMS_OUTPUT.PUT_LINE (
' Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END IF;
END;
BEGIN
SELECT employee_id, last_name
BULK COLLECT INTO enums, names
FROM employees
ORDER BY employee_id;
print_first_n(3);
print_first_n(6);
END;
/
結果:
First 3 employees: Employee #100: King Employee #101: Kochhar Employee #102: De Haan First 6 employees: Employee #100: King Employee #101: Kochhar Employee #102: De Haan Employee #103: Hunold Employee #104: Ernst Employee #105: Austin
例12-17では、SELECT BULK COLLECT INTO文を使用して、結果セットを選択してレコードのネストした表に入れています。
例12-17 レコードのネストした表に対するバルク選択
DECLARE
CURSOR c1 IS
SELECT first_name, last_name, hire_date
FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
stock_managers NameSet; -- nested table of records
BEGIN
-- Assign values to nested table of records:
SELECT first_name, last_name, hire_date
BULK COLLECT INTO stock_managers
FROM employees
WHERE job_id = 'ST_MAN'
ORDER BY hire_date;
-- Print nested table of records:
FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
stock_managers(i).hire_date || ' ' ||
stock_managers(i).last_name || ', ' ||
stock_managers(i).first_name
);
END LOOP;END;
/
結果:
01-MAY-03 Kaufling, Payam 18-JUL-04 Weiss, Matthew 10-APR-05 Fripp, Adam 10-OCT-05 Vollman, Shanta 16-NOV-07 Mourgos, Kevin
ここでのトピック
次の書式の文について説明します。
SELECT column BULK COLLECT INTO collection FROM table ...
columnおよびcollectionは、それぞれIN NOCOPYおよびOUT NOCOPYサブプログラム・パラメータに類似しており、PL/SQLによって参照方式で渡されます。参照方式で渡されるサブプログラム・パラメータと同様に、エイリアシングによって予期しない結果が発生する場合があります。
例12-18では、コレクションnumbers1から特定の値を選択し、その値を同じコレクションに格納することを意図しています。ところが、numbers1のすべての要素が削除されるという予期しない結果が発生します。この問題の回避策は、例12-19および例12-20を参照してください。
例12-18 予期しない結果が発生するSELECT BULK COLLECT INTO文
CREATE OR REPLACE TYPE numbers_type IS
TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS
numbers1 numbers_type := numbers_type(1,2,3,4,5);
BEGIN
DBMS_OUTPUT.PUT_LINE('Before SELECT statement');
DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
FOR j IN 1..numbers1.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
END LOOP;
--Self-selecting BULK COLLECT INTO clause:
SELECT a.COLUMN_VALUE
BULK COLLECT INTO numbers1
FROM TABLE(numbers1) a
WHERE a.COLUMN_VALUE > p.i
ORDER BY a.COLUMN_VALUE;
DBMS_OUTPUT.PUT_LINE('After SELECT statement');
DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
END p;
/
pの起動:
BEGIN
p(2);
END;
/
結果:
Before SELECT statement numbers1.COUNT() = 5 numbers1(1) = 1 numbers1(2) = 2 numbers1(3) = 3 numbers1(4) = 4 numbers1(5) = 5 After SELECT statement numbers1.COUNT() = 0 PL/SQL procedure successfully completed.
pの起動:
BEGIN
p(10);
END;
/
結果:
Before SELECT statement numbers1.COUNT() = 5 numbers1(1) = 1 numbers1(2) = 2 numbers1(3) = 3 numbers1(4) = 4 numbers1(5) = 5 After SELECT statement numbers1.COUNT() = 0
例12-19では、カーソルを使用して例12-18で意図されていた結果を実現します。
例12-19 カーソルによる例12-18の回避策
CREATE OR REPLACE TYPE numbers_type IS TABLE OF INTEGER / CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS numbers1 numbers_type := numbers_type(1,2,3,4,5); CURSOR c IS SELECT a.COLUMN_VALUE FROM TABLE(numbers1) a WHERE a.COLUMN_VALUE > p.i ORDER BY a.COLUMN_VALUE; BEGIN DBMS_OUTPUT.PUT_LINE('Before FETCH statement'); DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); FOR j IN 1..numbers1.COUNT() LOOP DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); END LOOP; OPEN c; FETCH c BULK COLLECT INTO numbers1; CLOSE c; DBMS_OUTPUT.PUT_LINE('After FETCH statement'); DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); IF numbers1.COUNT() > 0 THEN FOR j IN 1..numbers1.COUNT() LOOP DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); END LOOP; END IF; END p; /
pの起動:
BEGIN
p(2);
END;
/
結果:
Before FETCH statement numbers1.COUNT() = 5 numbers1(1) = 1 numbers1(2) = 2 numbers1(3) = 3 numbers1(4) = 4 numbers1(5) = 5 After FETCH statement numbers1.COUNT() = 3 numbers1(1) = 3 numbers1(2) = 4 numbers1(3) = 5
pの起動:
BEGIN
p(10);
END;
/
結果:
Before FETCH statement numbers1.COUNT() = 5 numbers1(1) = 1 numbers1(2) = 2 numbers1(3) = 3 numbers1(4) = 4 numbers1(5) = 5 After FETCH statement numbers1.COUNT() = 0
例12-20では、コレクションnumbers1から特定の値を選択し、その値を別のコレクションnumbers2に格納します。例12-20の実行速度は例12-19の場合より速くなります。
例12-20 2番目のコレクションによる例12-18の回避策
CREATE OR REPLACE TYPE numbers_type IS TABLE OF INTEGER / CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS numbers1 numbers_type := numbers_type(1,2,3,4,5); numbers2 numbers_type := numbers_type(0,0,0,0,0); BEGIN DBMS_OUTPUT.PUT_LINE('Before SELECT statement'); DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); FOR j IN 1..numbers1.COUNT() LOOP DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); END LOOP; DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT()); FOR j IN 1..numbers2.COUNT() LOOP DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j)); END LOOP; SELECT a.COLUMN_VALUE BULK COLLECT INTO numbers2 -- numbers2 appears here FROM TABLE(numbers1) a -- numbers1 appears here WHERE a.COLUMN_VALUE > p.i ORDER BY a.COLUMN_VALUE; DBMS_OUTPUT.PUT_LINE('After SELECT statement'); DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); IF numbers1.COUNT() > 0 THEN FOR j IN 1..numbers1.COUNT() LOOP DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); END LOOP; END IF; DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT()); IF numbers2.COUNT() > 0 THEN FOR j IN 1..numbers2.COUNT() LOOP DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j)); END LOOP; END IF; END p; /
pの起動:
BEGIN
p(2);
END;
/
結果:
Before SELECT statement numbers1.COUNT() = 5 numbers1(1) = 1 numbers1(2) = 2 numbers1(3) = 3 numbers1(4) = 4 numbers1(5) = 5 numbers2.COUNT() = 5 numbers2(1) = 0 numbers2(2) = 0 numbers2(3) = 0 numbers2(4) = 0 numbers2(5) = 0 After SELECT statement numbers1.COUNT() = 5 numbers1(1) = 1 numbers1(2) = 2 numbers1(3) = 3 numbers1(4) = 4 numbers1(5) = 5 numbers2.COUNT() = 3 numbers2(1) = 3 numbers2(2) = 4 numbers2(3) = 5 PL/SQL procedure successfully completed.
pの起動:
BEGIN
p(10);
END;
/
結果:
Before SELECT statement numbers1.COUNT() = 5 numbers1(1) = 1 numbers1(2) = 2 numbers1(3) = 3 numbers1(4) = 4 numbers1(5) = 5 numbers2.COUNT() = 5 numbers2(1) = 0 numbers2(2) = 0 numbers2(3) = 0 numbers2(4) = 0 numbers2(5) = 0 After SELECT statement numbers1.COUNT() = 5 numbers1(1) = 1 numbers1(2) = 2 numbers1(3) = 3 numbers1(4) = 4 numbers1(5) = 5 numbers2.COUNT() = 0
大量の行を戻すSELECT BULK COLLECT INTO文では、大規模なコレクションが生成されます。行数およびコレクション・サイズを制限するには、ROWNUM疑似列(『Oracle Database SQL言語リファレンス』を参照)またはSAMPLE句(『Oracle Database SQL言語リファレンス』を参照)を使用します。
例12-21では、最初のSELECT BULK COLLECT INTO文でROWNUMを使用して行数を50に制限し、2番目のSELECT BULK COLLECT INTO文でSAMPLEを使用して行数を全体の約10%に制限します。
結果セットをコレクションに格納する場合、行をループして異なる列を参照することは簡単です。この方法では非常に高速に処理されますが、メモリーも集中的に使用されます。この方法を頻繁に使用する場合、次のことに注意してください。
結果セットを1回ループするには、カーソルFOR LOOPを使用します(「カーソルFOR LOOP文による問合せ結果セットの処理」を参照)。
この方法を使用すると、結果セットのコピーを格納する際のメモリーのオーバーヘッドを回避できます。
結果セットをループして特定の値を検索したり、結果をフィルタリングしてより小さい結果セットにするかわりに、SELECT INTO文の問合せで検索またはフィルタリングを行います。
たとえば、単純な問合せではWHERE句を使用し、複数の結果セットを比較する問合せでは、INTERSECTやMINUSなどの集合演算子を使用します。集合演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
結果セットをループして各結果行に対して別の問合せを実行するかわりに、SELECT INTO文の問合せで副問合せを使用します(「副問合せによる問合せ結果セットの処理」を参照)。
結果セットをループして各結果行に対して別のDML文を実行するかわりに、FORALL文を使用します(「FORALL文」を参照)。
BULK COLLECT句付きのFETCH文(FETCH BULK COLLECT文とも呼ばれます)は、結果セット全体をフェッチして1つ以上のコレクション変数に入れます。詳細は、「FETCH文」を参照してください。
例12-22では、FETCH BULK COLLECT文を使用し、結果セット全体をフェッチして2つのコレクション(ネストした表)に入れます。
例12-22 2つのネストした表へのバルク・フェッチ
DECLARE
TYPE NameList IS TABLE OF employees.last_name%TYPE;
TYPE SalList IS TABLE OF employees.salary%TYPE;
CURSOR c1 IS
SELECT last_name, salary
FROM employees
WHERE salary > 10000
ORDER BY last_name;
names NameList;
sals SalList;
TYPE RecList IS TABLE OF c1%ROWTYPE;
recs RecList;
v_limit PLS_INTEGER := 10;
PROCEDURE print_results IS
BEGIN
-- Check if collections are empty:
IF names IS NULL OR names.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No results!');
ELSE
DBMS_OUTPUT.PUT_LINE('Result: ');
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i));
END LOOP;
END IF;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE ('--- Processing all results simultaneously ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
CLOSE c1;
print_results();
DBMS_OUTPUT.PUT_LINE ('--- Processing ' || v_limit || ' rows at a time ---');
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
EXIT WHEN names.COUNT = 0;
print_results();
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE ('--- Fetching records rather than columns ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO recs;
FOR i IN recs.FIRST .. recs.LAST
LOOP
-- Now all columns from result set come from one record
DBMS_OUTPUT.PUT_LINE (
' Employee ' || recs(i).last_name || ': $' || recs(i).salary
);
END LOOP;
END;
/
結果:
--- Processing all results simultaneously --- Result: Employee Abel: $11000 Employee Cambrault: $11000 Employee De Haan: $17000 Employee Errazuriz: $12000 Employee Fripp: $18540.29 Employee Greenberg: $12008 Employee Hartstein: $13000 Employee Higgins: $12008 Employee Kaufling: $17862 Employee King: $24000 Employee Kochhar: $17000 Employee Mourgos: $13113.87 Employee Ozer: $11500 Employee Partners: $13500 Employee Raphaely: $11000 Employee Russell: $14000 Employee Vishney: $10500 Employee Vollman: $14696.58 Employee Weiss: $22907.66 Employee Zlotkey: $10500 --- Processing 10 rows at a time --- Result: Employee Abel: $11000 Employee Cambrault: $11000 Employee De Haan: $17000 Employee Errazuriz: $12000 Employee Fripp: $18540.29 Employee Greenberg: $12008 Employee Hartstein: $13000 Employee Higgins: $12008 Employee Kaufling: $17862 Employee King: $24000 Result: Employee Kochhar: $17000 Employee Mourgos: $13113.87 Employee Ozer: $11500 Employee Partners: $13500 Employee Raphaely: $11000 Employee Russell: $14000 Employee Vishney: $10500 Employee Vollman: $14696.58 Employee Weiss: $22907.66 Employee Zlotkey: $10500 --- Fetching records rather than columns --- Employee Abel: $11000 Employee Cambrault: $11000 Employee De Haan: $17000 Employee Errazuriz: $12000 Employee Fripp: $18540.29 Employee Greenberg: $12008 Employee Hartstein: $13000 Employee Higgins: $12008 Employee Kaufling: $17862 Employee King: $24000 Employee Kochhar: $17000 Employee Mourgos: $13113.87 Employee Ozer: $11500 Employee Partners: $13500 Employee Raphaely: $11000 Employee Russell: $14000 Employee Vishney: $10500 Employee Vollman: $14696.58 Employee Weiss: $22907.66 Employee Zlotkey: $10500
例12-23では、FETCH BULK COLLECT文を使用し、結果セットをフェッチしてレコードのコレクション(ネストした表)に入れます。
例12-23 レコードのネストした表へのバルク・フェッチ
DECLARE
CURSOR c1 IS
SELECT first_name, last_name, hire_date
FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
stock_managers NameSet; -- nested table of records
TYPE cursor_var_type is REF CURSOR;
cv cursor_var_type;
BEGIN
-- Assign values to nested table of records:
OPEN cv FOR
SELECT first_name, last_name, hire_date
FROM employees
WHERE job_id = 'ST_MAN'
ORDER BY hire_date;
FETCH cv BULK COLLECT INTO stock_managers;
CLOSE cv;
-- Print nested table of records:
FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
stock_managers(i).hire_date || ' ' ||
stock_managers(i).last_name || ', ' ||
stock_managers(i).first_name
);
END LOOP;END;
/
結果:
01-MAY-03 Kaufling, Payam 18-JUL-04 Weiss, Matthew 10-APR-05 Fripp, Adam 10-OCT-05 Vollman, Shanta 16-NOV-07 Mourgos, Kevin
大量の行を戻すFETCH BULK COLLECT文では、大規模なコレクションが生成されます。行数およびコレクション・サイズを制限するには、LIMIT句を使用します。
例12-24では、LOOP文が繰り返されるたびに、FETCH文によって10行(またはそれ以下)が連想配列empidsにフェッチされます(前の値は上書きされます)。
例12-24 LIMITによるバルクFETCHの制限
DECLARE
TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR c1 IS
SELECT employee_id
FROM employees
WHERE department_id = 80
ORDER BY employee_id;
empids numtab;
BEGIN
OPEN c1;
LOOP -- Fetch 10 rows or fewer in each iteration
FETCH c1 BULK COLLECT INTO empids LIMIT 10;
DBMS_OUTPUT.PUT_LINE ('------- Results from One Bulk Fetch --------');
FOR i IN 1..empids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE ('Employee Id: ' || empids(i));
END LOOP;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
/
結果:
------- Results from One Bulk Fetch -------- Employee Id: 145 Employee Id: 146 Employee Id: 147 Employee Id: 148 Employee Id: 149 Employee Id: 150 Employee Id: 151 Employee Id: 152 Employee Id: 153 Employee Id: 154 ------- Results from One Bulk Fetch -------- Employee Id: 155 Employee Id: 156 Employee Id: 157 Employee Id: 158 Employee Id: 159 Employee Id: 160 Employee Id: 161 Employee Id: 162 Employee Id: 163 Employee Id: 164 ------- Results from One Bulk Fetch -------- Employee Id: 165 Employee Id: 166 Employee Id: 167 Employee Id: 168 Employee Id: 169 Employee Id: 170 Employee Id: 171 Employee Id: 172 Employee Id: 173 Employee Id: 174 ------- Results from One Bulk Fetch -------- Employee Id: 175 Employee Id: 176 Employee Id: 177 Employee Id: 179
BULK COLLECT句付きのRETURNING INTO句(RETURNING BULK COLLECT INTO句とも呼ばれます)は、INSERT文、UPDATE文、DELETE文またはEXECUTE IMMEDIATE文で使用できます。RETURNING BULK COLLECT INTO句を使用すると、文によってその結果セットが1つ以上のコレクションに格納されます。詳細は、「RETURNING INTO句」を参照してください。
例12-25では、RETURNING BULK COLLECT INTO句付きのDELETE文を使用して、表から行を削除し、それらの行を2つのコレクション(ネストした表)に戻します。
例12-25 2つのネストした表に対する削除行の戻し
DROP TABLE emp_temp; CREATE TABLE emp_temp AS SELECT * FROM employees ORDER BY employee_id; DECLARE TYPE NumList IS TABLE OF employees.employee_id%TYPE; enums NumList; TYPE NameList IS TABLE OF employees.last_name%TYPE; names NameList; BEGIN DELETE FROM emp_temp WHERE department_id = 30 RETURNING employee_id, last_name BULK COLLECT INTO enums, names; DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:'); FOR i IN enums.FIRST .. enums.LAST LOOP DBMS_OUTPUT.PUT_LINE ('Employee #' || enums(i) || ': ' || names(i)); END LOOP; END; /
結果:
Deleted 6 rows: Employee #114: Raphaely Employee #115: Khoo Employee #116: Baida Employee #117: Tobias Employee #118: Himuro Employee #119: Colmenares
FORALL文では、DML文にRETURNING BULK COLLECT INTO句を含めることができます。FORALL文が繰り返されるたびに、DML文は前の値を上書きすることなく、指定された値を指定されたコレクションに格納します(この動作は、FOR LOOP文における同じDML文の処理と同様です)。
例12-26では、FORALL文で、RETURNING BULK COLLECT INTO句が含まれるDELETE文を実行します。FORALL文が繰り返されるたびに、DELETE文は、削除された行のemployee_idおよびdepartment_idの値をそれぞれコレクションe_idsおよびd_idsに格納します。
例12-26 FORALL文でのRETURN BULK COLLECT INTO付きのDELETE
DROP TABLE emp_temp; CREATE TABLE emp_temp AS SELECT * FROM employees ORDER BY employee_id, department_id; DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10,20,30); TYPE enum_t IS TABLE OF employees.employee_id%TYPE; e_ids enum_t; TYPE dept_t IS TABLE OF employees.department_id%TYPE; d_ids dept_t; BEGIN FORALL j IN depts.FIRST..depts.LAST DELETE FROM emp_temp WHERE department_id = depts(j) RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids; DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:'); FOR i IN e_ids.FIRST .. e_ids.LAST LOOP DBMS_OUTPUT.PUT_LINE ( 'Employee #' || e_ids(i) || ' from dept #' || d_ids(i) ); END LOOP; END; /
結果:
Deleted 9 rows: Employee #200 from dept #10 Employee #201 from dept #20 Employee #202 from dept #20 Employee #114 from dept #30 Employee #115 from dept #30 Employee #116 from dept #30 Employee #117 from dept #30 Employee #118 from dept #30 Employee #119 from dept #30
例12-27は、例12-26と類似していますが、FORALL文のかわりにFOR LOOP文を使用しています。
例12-27 FOR LOOP文でのRETURN BULK COLLECT INTO付きのDELETE
DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10,20,30); TYPE enum_t IS TABLE OF employees.employee_id%TYPE; e_ids enum_t; TYPE dept_t IS TABLE OF employees.department_id%TYPE; d_ids dept_t; BEGIN FOR j IN depts.FIRST..depts.LAST LOOP DELETE FROM emp_temp WHERE department_id = depts(j) RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids; END LOOP; DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:'); FOR i IN e_ids.FIRST .. e_ids.LAST LOOP DBMS_OUTPUT.PUT_LINE ( 'Employee #' || e_ids(i) || ' from dept #' || d_ids(i) ); END LOOP; END; /
結果:
Deleted 6 rows: Employee #114 from dept #30 Employee #115 from dept #30 Employee #116 from dept #30 Employee #117 from dept #30 Employee #118 from dept #30 Employee #119 from dept #30
クライアント・プログラム(OCIプログラムやPro*Cプログラムなど)は、PL/SQLの無名ブロックを使用してホスト配列をバルク・バインド入出力できます。これは、コレクションをデータベース・サーバーとの間でやり取りするのに最も効率的な方法です。
クライアント・プログラムで、値を宣言し、無名ブロックで参照されるホスト変数にそれらの値を代入します。無名ブロックでは、各ホスト変数名にコロン(:)を接頭辞として付け、PL/SQLのコレクション変数名と区別します。クライアント・プログラムが実行されると、データベース・サーバーでPL/SQLの無名ブロックが実行されます。
例12-28では、無名ブロックでFORALL文を使用してホスト入力配列をバルク・バインドします。FORALL文において、DELETE文は、スカラーlower、upper、emp_idおよび配列deptsという4つのホスト変数を参照します。
パイプライン・テーブル・ファンクションの連鎖は、複数のデータ変換を実行する場合に効率的な方法です。
|
注意: パイプライン・テーブル・ファンクションは、データベース・リンクを介して実行することはできません。パイプライン・テーブル・ファンクションの戻り型がユーザー定義のSQL型であり、単一データベース内でのみ使用可能であるためです(『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照)。パイプライン・テーブル・ファンクションの戻り型がPL/SQL型のように見える場合でも、そのPL/SQL型は実際にはデータベースによって対応するユーザー定義のSQL型に変換されます。 |
ここでのトピック
テーブル・ファンクションは、行のコレクション(ネストした表またはVARRAY)を戻すユーザー定義のPL/SQLファンクションです。SELECT文のTABLE句の内部でテーブル・ファンクションを起動することで、データベース表のようにこのコレクションから要素を選択できます。次に例を示します。
SELECT * FROM TABLE(table_function_name(parameter_list))
(SELECT文のTABLE句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。)
テーブル・ファンクションは、入力として行のコレクションを使用することができます(入力パラメータとしてネストした表、VARRAYまたはカーソル変数を含めることができます)。したがって、テーブル・ファンクションtf1からの出力をテーブル・ファンクションtf2に入力したり、tf2からの出力をテーブル・ファンクションtf3に入力することなどができます。詳細は、「パイプライン・テーブル・ファンクションの連鎖」を参照してください。
テーブル・ファンクションのパフォーマンスを向上するには、次の操作を実行します。
PARALLEL_ENABLEオプションを使用して、ファンクションのパラレル実行を有効にします。
パラレル実行が有効化されたファンクションは、同時に実行できます。
Oracle Streamsを使用して、次のプロセスにファンクションの結果をストリーム化して直接送ります。
ストリームによって、プロセス間の中間的なステージングが排除されます。Oracle Streamsの詳細は、『Oracle Streams概要および管理』を参照してください。
PIPELINEDオプションを使用して、ファンクションの結果をパイプライン化します。
パイプライン・テーブル・ファンクションは、行を処理した直後に起動元に行を戻し、行の処理を継続します。問合せで1つの結果行を戻す前にコレクション全体を組み立ててサーバーに戻す必要がないため、応答時間が短縮されます。(オブジェクト・キャッシュでコレクション全体をマテリアライズする必要がないため、ファンクションのメモリー消費量も減少します。)
|
関連項目: パイプライン・テーブル・ファンクションおよびパラレル・テーブル・ファンクションを使用する方法の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。 |
パイプライン・テーブル・ファンクションは、スタンドアロン・ファンクションまたはパッケージ・ファンクションのいずれかである必要があります。
PIPELINEDオプション(必須)
スタンドアロン・ファンクションでは、CREATE FUNCTION文にPIPELINEDオプションを指定します(構文は、「CREATE FUNCTION文」を参照してください)。パッケージ・ファンクションでは、ファンクション宣言とファンクション定義の両方にPIPELINEDオプションを指定します(構文は、「ファンクションの宣言および定義」を参照してください)。
PARALLEL_ENABLEオプション(推奨)
パフォーマンスを向上するには、PARALLEL_ENABLEオプションを指定して、パイプライン・テーブル・ファンクションのパラレル実行を有効にします。
AUTONOMOUS_TRANSACTIONプラグマ
パイプライン・テーブル・ファンクションでDML文を実行する場合、AUTONOMOUS_TRANSACTIONプラグマ(「AUTONOMOUS_TRANSACTIONプラグマ」を参照)を使用すると、ファンクションが自律型になります。その後、パラレル実行中に、ファンクションの各インスタンスが独立したトランザクションを作成します。
DETERMINISTICオプション(推奨)
パイプライン・テーブル・ファンクションを同じ問合せまたは別の問合せで複数回起動すると、基礎となる実装が複数回実行されます。ファンクションが決定的である場合、DETERMINISTICオプションを指定します(「DETERMINISTIC」を参照)。
パラメータ
通常、パイプライン・テーブル・ファンクションには、1つ以上のカーソル変数パラメータが含まれます。ファンクション・パラメータとしてのカーソル変数の詳細は、「サブプログラム・パラメータとしてのカーソル変数」を参照してください。
RETURNデータ型
パイプライン・テーブル・ファンクションが戻す値のデータ型は、スキーマ・レベルまたはパッケージ内で定義されたコレクション型である必要があります(そのため、連想配列型にはできません)。コレクション型の要素は、PL/SQLによってのみサポートされるデータ型(PLS_INTEGERやBOOLEANなど)ではなく、SQLデータ型にする必要があります。コレクション型の詳細は、「コレクション型」を参照してください。SQLデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SQLデータ型のANYTYPE、ANYDATAおよびANYDATASETを使用して、オブジェクト型やコレクション型などの他のSQL型の型記述、データ・インスタンスおよびデータ・インスタンス・セットを動的にカプセル化してアクセスできます。また、これらの型を使用すると、匿名コレクション型などの名前を持たない型を作成できます。これらの型の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
PIPE ROW文
パイプライン・テーブル・ファンクションの内部でPIPE ROW文を使用すると、制御を起動元に戻すことなく、起動元にコレクション要素を戻すことができます。PIPE ROW文の構文およびセマンティクスの詳細は、「PIPE ROW文」を参照してください。
RETURN文
他のすべてのファンクションと同様に、パイプライン・テーブル・ファンクションのすべての実行パスは、RETURN文に導かれ、それによって制御が起動元に戻される必要があります。ただし、パイプライン・テーブル・ファンクションでは、RETURN文で起動元に値を戻す必要はありません。RETURN文の構文およびセマンティクスの詳細は、「RETURN文」を参照してください。
例
例12-29では、パイプライン・テーブル・ファンクションf1を含むパッケージを作成し、f1が戻す行のコレクションから要素を選択します。
例12-29 パイプライン・テーブル・ファンクションの作成および起動
CREATE OR REPLACE PACKAGE pkg1 AS TYPE numset_t IS TABLE OF NUMBER; FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED; END pkg1; / CREATE PACKAGE BODY pkg1 AS -- FUNCTION f1 returns a collection of elements (1,2,3,... x) FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS BEGIN FOR i IN 1..x LOOP PIPE ROW(i); END LOOP; RETURN; END f1; END pkg1; / SELECT * FROM TABLE(pkg1.f1(5));
結果:
COLUMN_VALUE
------------
1
2
3
4
5
5 rows selected.
カーソル変数パラメータを持つパイプライン・テーブル・ファンクションは、変換ファンクションとして使用できます。ファンクションは、カーソル変数を使用して入力行をフェッチします。ファンクションは、PIPE ROW文を使用して、変換された1つ以上の行を起動元にパイプします。FETCH文およびPIPE ROW文がLOOP文の内側にある場合、ファンクションで複数の入力行を変換できます。
例12-30では、パイプライン・テーブル・ファンクションによって、employees表の各選択行がネストした表の2つの行に変換され、起動元のSELECT文にパイプされます。仮カーソル変数パラメータに対応する実パラメータは、CURSOR式です(詳細は、「パイプライン・テーブル・ファンクションへのCURSOR式の引渡し」を参照してください)。
例12-30 パイプライン・テーブル・ファンクションによる各行の2つの行への変換
CREATE OR REPLACE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30)
);
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED;
END refcur_pkg;
/
CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec; -- input row
EXIT WHEN p%NOTFOUND;
out_rec.var_num := in_rec.employee_id;
out_rec.var_char1 := in_rec.first_name;
out_rec.var_char2 := in_rec.last_name;
PIPE ROW(out_rec); -- first transformed output row
out_rec.var_char1 := in_rec.email;
out_rec.var_char2 := in_rec.phone_number;
PIPE ROW(out_rec); -- second transformed output row
END LOOP;
CLOSE p;
RETURN;
END f_trans;
END refcur_pkg;
/
SELECT * FROM TABLE (
refcur_pkg.f_trans (
CURSOR (SELECT * FROM employees WHERE department_id = 60)
)
);
結果:
VAR_NUM VAR_CHAR1 VAR_CHAR2
---------- ------------------------------ ------------------------------
103 Alexander Hunold
103 AHUNOLD 590.423.4567
104 Bruce Ernst
104 BERNST 590.423.4568
105 David Austin
105 DAUSTIN 590.423.4569
106 Valli Pataballa
106 VPATABAL 590.423.4560
107 Diana Lorentz
107 DLORENTZ 590.423.5567
10 rows selected.
パイプライン・テーブル・ファンクションのtf1とtf2を連鎖するには、tf1の出力をtf2の入力にします。次に例を示します。
SELECT * FROM TABLE(tf2(CURSOR(SELECT * FROM TABLE(tf1()))));
tf1によってパイプ出力される行は、tf2の仮入力パラメータと互換性のある実パラメータである必要があります。
連鎖されたパイプライン・テーブル・ファンクションでパラレル実行が有効化されていると、各ファンクションは異なるプロセス(またはプロセスのセット)で実行されます。
名前付きカーソルは、パイプライン・テーブル・ファンクションを起動する問合せに関連付けることができます。このようなカーソルに特別なフェッチ・セマンティクスはなく、また、このようなカーソル変数に特別な代入セマンティクスはありません。
ただし、SQLオプティマイザでは、PL/SQL文にまたがる最適化は行われません。したがって、例12-31で、2番目のPL/SQL文には2つのSQL文を実行するオーバーヘッドがありますが、1番目のPL/SQL文は2番目の文より遅くなり、この結果は、1番目のPL/SQL文の2つのSQL文の間でファンクション結果がパイプされる場合でも変わりません。
例12-31で、fおよびgがパイプライン・テーブル・ファンクションであり、各ファンクションでカーソル変数パラメータを受け入れるとします。1番目のPL/SQL文は、カーソル変数rを、fを起動する問合せに関連付けて、rをgに渡します。2番目のPL/SQL文は、CURSOR式をfとgの両方に渡します。
例12-31 パイプライン・テーブル・ファンクションの結果からのフェッチ
DECLARE r SYS_REFCURSOR; ... -- First PL/SQL statement (slower): BEGIN OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab))); SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r)); -- NOTE: When g completes, it closes r. END; -- Second PL/SQL statement (faster): SELECT * FROM TABLE(g(CURSOR(SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab)))))); /
例12-31のように、パイプライン・テーブル・ファンクションのカーソル変数パラメータの実パラメータには、カーソル変数またはCURSOR式を使用できますが、後者の方が効率的です。
|
注意: SQLのSELECT文でCURSOR式をファンクションに渡す場合、参照先のカーソルは、ファンクションの実行開始時にオープンされ、ファンクションの完了時にクローズされます。 |
例12-32では、2つのカーソル変数パラメータがあるパイプライン・テーブル・ファンクションを含むパッケージを作成し、実パラメータにCURSOR式を使用してSELECT文のファンクションを起動します。
例12-32 2つのカーソル変数パラメータを使用するパイプライン・テーブル・ファンクション
CREATE OR REPLACE PACKAGE refcur_pkg IS
TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30)
);
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION g_trans (p1 refcur_t1, p2 refcur_t2) RETURN outrecset PIPELINED;
END refcur_pkg;
/
CREATE PACKAGE BODY refcur_pkg IS
FUNCTION g_trans (
p1 refcur_t1,
p2 refcur_t2
) RETURN outrecset PIPELINED
IS
out_rec outrec_typ;
in_rec1 p1%ROWTYPE;
in_rec2 p2%ROWTYPE;
BEGIN
LOOP
FETCH p2 INTO in_rec2;
EXIT WHEN p2%NOTFOUND;
END LOOP;
CLOSE p2;
LOOP
FETCH p1 INTO in_rec1;
EXIT WHEN p1%NOTFOUND;
-- first row
out_rec.var_num := in_rec1.employee_id;
out_rec.var_char1 := in_rec1.first_name;
out_rec.var_char2 := in_rec1.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_num := in_rec2.department_id;
out_rec.var_char1 := in_rec2.department_name;
out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
PIPE ROW(out_rec);
END LOOP;
CLOSE p1;
RETURN;
END g_trans;
END refcur_pkg;
/
SELECT * FROM TABLE (
refcur_pkg.g_trans (
CURSOR (SELECT * FROM employees WHERE department_id = 60),
CURSOR (SELECT * FROM departments WHERE department_id = 60)
)
);
結果:
VAR_NUM VAR_CHAR1 VAR_CHAR2
---------- ------------------------------ ------------------------------
103 Alexander Hunold
60 IT 1400
104 Bruce Ernst
60 IT 1400
105 David Austin
60 IT 1400
106 Valli Pataballa
60 IT 1400
107 Diana Lorentz
60 IT 1400
10 rows selected.
例12-33では、パイプライン・テーブル・ファンクションを集計ファンクションとして使用し、入力行のセットを取得して1つの結果を戻します。ファンクションの結果は、SELECT文で選択します。(疑似列COLUMN_VALUEの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。)
例12-33 集計ファンクションとしてのパイプライン・テーブル・ファンクション
DROP TABLE gradereport;
CREATE TABLE gradereport (
student VARCHAR2(30),
subject VARCHAR2(30),
weight NUMBER,
grade NUMBER
);
INSERT INTO gradereport (student, subject, weight, grade)
VALUES ('Mark', 'Physics', 4, 4);
INSERT INTO gradereport (student, subject, weight, grade)
VALUES ('Mark','Chemistry', 4, 3);
INSERT INTO gradereport (student, subject, weight, grade)
VALUES ('Mark','Maths', 3, 3);
INSERT INTO gradereport (student, subject, weight, grade)
VALUES ('Mark','Economics', 3, 4);
CREATE PACKAGE pkg_gpa IS
TYPE gpa IS TABLE OF NUMBER;
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED;
END pkg_gpa;
/
CREATE PACKAGE BODY pkg_gpa IS
FUNCTION weighted_average (input_values SYS_REFCURSOR)
RETURN gpa PIPELINED
IS
grade NUMBER;
total NUMBER := 0;
total_weight NUMBER := 0;
weight NUMBER := 0;
BEGIN
LOOP
FETCH input_values INTO weight, grade;
EXIT WHEN input_values%NOTFOUND;
total_weight := total_weight + weight; -- Accumulate weighted average
total := total + grade*weight;
END LOOP;
PIPE ROW (total / total_weight);
RETURN; -- returns single result
END weighted_average;
END pkg_gpa;
/
SELECT w.column_value "weighted result" FROM TABLE (
pkg_gpa.weighted_average (
CURSOR (SELECT weight, grade FROM gradereport)
)
) w;
結果:
weighted result
---------------
3.5
1 row selected.
パイプライン・テーブル・ファンクションが戻す表を、DELETE文、INSERT文、UPDATE文またはMERGEのターゲット表にすることはできません。ただし、このような表のビューを作成し、そのビューに対してINSTEAD OFトリガーを作成できます。INSTEAD OFトリガーの詳細は、「INSTEAD OF DMLトリガー」を参照してください。
|
関連項目: CREATE VIEW文の詳細は、 |
事前定義の例外NO_DATA_NEEDEDを理解する必要があるのは、次の2つの場合です。
PIPE ROW文が存在するブロックにOTHERS例外ハンドラを含める場合
PIPE ROW文を使用するコードの次に、クリーンアップ・プロシージャを続ける必要がある場合
通常、クリーンアップ・プロシージャは、コードで必要とされなくなったリソースを解放します。
パイプライン・テーブル・ファンクションの起動元がファンクションからの行をそれ以上必要としなくなると、PIPE ROW文によってNO_DATA_NEEDEDが呼び出されます。パイプライン・テーブル・ファンクションでNO_DATA_NEEDEDを処理しない場合、例12-34に示すとおり、ファンクションの起動は終了しますが、起動元の文は終了しません。パイプライン・テーブル・ファンクションでNO_DATA_NEEDEDを処理する場合、例12-35に示すとおり、例外ハンドラによって不要になったリソースを解放できます。
例12-34では、パイプライン・テーブル・ファンクションpipe_rowsでNO_DATA_NEEDED例外を処理しません。pipe_rowsを起動するSELECT文では、4つの行のみを必要とします。そのため、pipe_rowsの5回目の起動時に、PIPE ROW文によって例外NO_DATA_NEEDEDが呼び出されます。pipe_rowsの5回目の起動は終了しますが、SELECT文は終了しません。
例12-34 パイプライン・テーブル・ファンクションで処理されないNO_DATA_NEEDED
CREATE TYPE t IS TABLE OF NUMBER
/
CREATE OR REPLACE FUNCTION pipe_rows RETURN t PIPELINED IS
n NUMBER := 0;
BEGIN
LOOP
n := n + 1;
PIPE ROW (n);
END LOOP;
END pipe_rows;
/
SELECT COLUMN_VALUE
FROM TABLE(pipe_rows())
WHERE ROWNUM < 5
/
結果:
COLUMN_VALUE
------------
1
2
3
4
4 rows selected.
PIPE ROW文が存在するブロックの例外処理部に、予期しない例外を処理するためのOTHERS例外ハンドラを含める場合、その例外処理部に予期されるNO_DATA_NEEDED例外のための例外ハンドラも含める必要があります。そうしない場合、NO_DATA_NEEDED例外は、OTHERS例外ハンドラで処理され、予期しないエラーとして扱われます。次の例外ハンドラでは、リカバリ不可能なエラーとして扱われるかわりに、NO_DATA_NEEDED例外が再度呼び出されます。
EXCEPTION WHEN NO_DATA_NEEDED THEN RAISE; WHEN OTHERS THEN -- (Put error-logging code here) RAISE_APPLICATION_ERROR(-20000, 'Fatal error.'); END;
例12-35では、External_Sourceパッケージに次のパブリック項目が含まれると想定します。
プロシージャInit: Next_Rowが必要とするリソースの割当てと初期化を行います。
ファンクションNext_Row: 特定の外部ソースのデータを戻し、外部ソースのデータがなくなるとユーザー定義の例外Done(同じくパッケージ内のパブリック項目)を呼び出します。
プロシージャClean_Up: Initで割り当てたリソースを解放します。
パイプライン・テーブル・ファンクションget_external_source_dataは、External_Source.Next_Rowを起動して、次のいずれかの状態になるまで外部ソースから行をパイプします。
外部ソースの行がなくなった場合。
この場合、External_Source.Next_Rowファンクションによってユーザー定義の例外External_Source.Doneが呼び出されます。
get_external_source_dataで行を必要としなくなった場合
この場合、get_external_source_dataのPIPE ROW文によってNO_DATA_NEEDED例外が呼び出されます。
どちらの場合でも、get_external_source_dataのブロックbにある例外ハンドラによって、Next_Rowが使用していたリソースを解放するExternal_Source.Clean_Upが起動されます。
例12-35 パイプライン・テーブル・ファンクションで処理されるNO_DATA_NEEDED
CREATE OR REPLACE FUNCTION get_external_source_data
RETURN t AUTHID DEFINER PIPELINED IS
BEGIN
External_Source.Init(); -- Initialize.
<<b>> BEGIN
LOOP -- Pipe rows from external source.
PIPE ROW (External_Source.Next_Row());
END LOOP;
EXCEPTION
WHEN External_Source.Done THEN -- When no more rows are available,
External_Source.Clean_Up(); -- clean up.
WHEN NO_DATA_NEEDED THEN -- When no more rows are needed,
External_Source.Clean_Up(); -- clean up.
RAISE NO_DATA_NEEDED; -- Optional, equivalent to RETURN.
END b;
END get_external_source_data;
/
DBMS_PARALLEL_EXECUTEパッケージを使用すると、次の2つの手順で、大規模な表のデータをパラレルで増分更新できます。
表内の一連の行を、より小さいチャンクにグループ化します。
必要なUPDATE文をパラレルでチャンクに適用し、1つのチャンクの処理が終わるたびにコミットします。
大量のデータを更新する場合は、常にこの方法をお薦めします。この方法には次のメリットがあります。
表全体をロックするのではなく、一度に1つの行セットのみをロックするため、比較的短時間で済みます。
操作全体が完了する前になんらかの障害が発生した場合でも、その時点までに終了していた作業の結果は失われません。
ロールバック用の領域の消費が削減されます。
パフォーマンスが向上します。
|
関連項目: DBMS_PARALLEL_EXECUTEパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』 |
PL/Scopeは、PL/SQLソース・テキストからユーザー定義の識別子に関するデータを抽出、編成および格納します。静的データ・ディクショナリ・ビュー*_IDENTIFIERSを使用してソース・テキストの識別子データを取り出すことができます。詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
大規模なPL/SQLプログラムでのパフォーマンスの問題を切り出すことができるように、PL/SQLには、PL/SQLパッケージとして実装される次のツールが用意されています。
| ツール | パッケージ | 説明 |
|---|---|---|
| プロファイラAPI | DBMS_PROFILER |
PL/SQLプログラムが各行および各サブプログラムに費やす時間を計算します。
プロファイル対象のユニットに対する ランタイム統計をデータベース表に保存します。このデータベース表は、問い合せることができます。 |
| トレースAPI | DBMS_TRACE |
サブプログラムの実行順序をトレースできます。
トレースするサブプログラムおよびトレース・レベルを指定できます。 ランタイム統計をデータベース表に保存します。このデータベース表は、問い合せることができます。 |
| PL/SQL階層型プロファイラ | DBMS_HPROF |
PL/SQLプログラムの動的な実行プログラム・プロファイルをサブプログラムの起動ごとにまとめてレポートします。SQL実行時間とPL/SQL実行時間を別々に示します。
特別なソースまたはコンパイル時の準備は必要ありません。 HTML形式でレポートを生成します。カスタム・レポートの生成用に結果をリレーショナル形式でデータベース表に格納する(サードパーティ・ツールで提供されるような)オプションを提供します。 |
ここでのトピック
PL/SQL階層型プロファイラの詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
プロファイラAPI(プロファイラ)は、PL/SQLパッケージDBMS_PROFILERとして実装されます。PL/SQLパッケージDBMS_PROFILERのサービスによって、PL/SQLプログラムが各行および各サブプログラムに費やす時間が計算され、それらの統計がデータベース表に保存されます。このデータベース表は、問い合せることができます。
|
注意: プロファイラは、CREATE権限を持つユニットに対してのみ使用できます。PL/SQL階層型プロファイラの使用にCREATE権限は必要ありません(『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照)。 |
プロファイラを使用するには、次の手順を実行します。
プロファイル・セッションを開始します。
十分な範囲のコードを取得できるまでPL/SQLプログラムを実行します。
収集されたデータをデータベースにフラッシュします。
プロファイル・セッションを停止します。
プロファイラでデータを収集したら、次の操作を実行できます。
パフォーマンス・データを含むデータベース表を問い合せます。
ほとんどの実行時間を使用するサブプログラムおよびパッケージを識別します。
特定のデータ構造へのアクセスおよび特定のコード・セグメントの実行に、プログラムでより時間がかかっている原因を判断します。
SQL文、ループ、再帰ファンクションなどの考えられるパフォーマンスのボトルネックを調べます。
分析結果に基づいて、不適切なデータ構造を置き換え、低速のアルゴリズムを書きなおします。
たとえば、データが急増したために、線形検索をバイナリ検索に置き換える必要が出てくることがあります。
DBMS_PROFILERサブプログラムの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
トレースAPI(トレース)は、PL/SQLパッケージDBMS_TRACEとして実装されます。PL/SQLパッケージDBMS_TRACEのサービスによって、サブプログラムまたは例外ごとに実行がトレースされ、それらの統計がデータベース表に保存されます。このデータベース表は、問い合せることができます。
トレースを使用するには、次の手順を実行します。
(オプション)トレースを特定のサブプログラムに制限し、トレース・レベルを選択します。
大規模なプログラムのすべてのサブプログラムおよび例外をトレースすると、大量のデータが生成されて管理が困難になることがあります。
トレース・セッションを開始します。
PL/SQLプログラムを実行します。
トレース・セッションを停止します。
トレースでデータを収集した後で、パフォーマンス・データが含まれているデータベース表を問い合せて、プロファイラからパフォーマンス・データを分析する方法と同じ方法で分析できます(「プロファイラAPI: DBMS_PROFILERパッケージ」を参照)。
DBMS_TRACEサブプログラムの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
通常、PL/SQLユニットをコンパイルして、システム固有のコード(プロセッサに依存するシステム・コード)にすると、PL/SQLユニットをスピードアップできます。システム固有のコードは、SYSTEM表領域に格納されます。
Oracle Databaseが提供するユニットを含め、すべてのタイプのPL/SQLユニットをネイティブ・コンパイルできます。
ネイティブ・コンパイルされたプログラム・ユニットは、共有サーバー構成(以前のマルチスレッド・サーバー)やOracle Real Application Clusters(Oracle RAC)などのすべてのサーバー環境で動作します。
ほとんどのプラットフォームで、PL/SQLネイティブ・コンパイルに特別な設定またはメンテナンスは必要ありません。一部のプラットフォームでは、DBAによるオプションの構成が必要な場合もあります。
|
関連項目:
|
PL/SQLのネイティブ・コンパイルを有効にしてパフォーマンスが改善される度合いは、テストによって確認できます。
PL/SQLのネイティブ・コンパイルを使用するとデータベース操作で大幅にパフォーマンスが改善されると判断した場合、ネイティブ・モードでデータベース全体をコンパイルすることをお薦めします(これを行うには、DBA権限が必要です)。これによって、独自のコードと、Oracle Database提供のPL/SQLパッケージへのコールの両方がスピードアップします。
ここでのトピック
* DBA権限が必要です。
PL/SQLユニットをネイティブ・モードでコンパイルするか、解釈済モードでコンパイルするかは、開発サイクルの段階およびプログラム・ユニットの内容によって決まります。
プログラム・ユニットをデバッグし、頻繁に再コンパイルしている場合は、解釈済モードに次のメリットがあります。
(ネイティブ・モードでコンパイルされたプログラム・ユニットではなく)解釈済モードでコンパイルされたプログラム・ユニットに対しては、PL/SQLデバッグ・ツールを使用できます。
解釈済モードのコンパイルは、ネイティブ・モードでのコンパイルより高速です。
開発のデバッグ・フェーズ後、ネイティブ・モードでPL/SQLユニットをコンパイルするかどうかを判断する際は次のことを考慮してください。
PL/SQLのネイティブ・コンパイルによって、計算集中型のプロシージャ操作のパフォーマンスは大幅に改善されます。この例には、データ・ウェアハウス・アプリケーションや、サーバー側でデータを大幅に変換して表示するアプリケーションなどがあります。
PL/SQLのネイティブ・コンパイルによって、SQLの実行にほとんどの時間を費やすPL/SQLサブプログラムのパフォーマンスはほとんど改善されません。
多数のプログラム・ユニット(通常は15,000以上)をシステム固有の実行用にコンパイルし、同時にアクティブにすると、大量の共有メモリーが必要になるため、システムのパフォーマンスに影響することがあります。
ネイティブ・コンパイルを使用しない場合、PL/SQLユニットのPL/SQL文は、中間形式のシステム・コードにコンパイルされます。このコードは、カタログに格納され、実行時に解釈されます。
PL/SQLのネイティブ・コンパイルを使用する場合、PL/SQLユニットのPL/SQL文はシステム固有のコードにコンパイルされ、カタログに格納されます。システム固有のコードは、実行時に解釈する必要がないため、高速で実行されます。
ネイティブ・コンパイルはPL/SQL文にのみ適用されるため、SQL文のみを使用するPL/SQLユニットの場合、ネイティブ・コンパイルしても高速に実行されない可能性がありますが、対応する解釈済コードより低速になることはありません。コンパイルされたコードと解釈済コードは同じライブラリをコールするため、アクションは同じです。
ネイティブ・コンパイルされたPL/SQLユニットを初めて実行すると、このユニットは、SYSTEM表領域から共有メモリーにフェッチされます。プログラム・ユニットを起動するセッションの数に関係なく、共有メモリーにはそのコピーが1つのみ含まれています。プログラム・ユニットが使用されていない場合、そのプログラム・ユニットが使用している共有メモリーを解放してメモリー・ロードを軽減できます。
ネイティブ・コンパイルされたサブプログラムと解釈済サブプログラムは相互に起動し合うことができます。
PL/SQLのネイティブ・コンパイルは、Oracle Real Application Clusters(Oracle RAC)環境では透過的に動作します。
PLSQL_CODE_TYPEコンパイル・パラメータによって、PL/SQLコードをネイティブ・コンパイルするか解釈済にするかが決まります。このコンパイル・パラメータの詳細は、「PL/SQLユニットおよびコンパイル・パラメータ」を参照してください。
無効化したPL/SQLモジュールでは、再コンパイルが自動的に行われます。たとえば、ネイティブ・コンパイルされたPL/SQLサブプログラムが依存するオブジェクトが変更されると、サブプログラムは無効になります。同じサブプログラムが次にコールされたとき、データベースはサブプログラムを自動的に再コンパイルします。PLSQL_CODE_TYPE設定はサブプログラムごとにライブラリ・ユニットに格納されるため、自動再コンパイルではこの格納された設定をコード型として使用します。
明示的な再コンパイルで、格納されているPLSQL_CODE_TYPE設定が使用されるとはかぎりません。格納されている設定を明示的な再コンパイルで使用する条件は、「PL/SQLユニットおよびコンパイル・パラメータ」を参照してください。
DBA権限を持っている場合、コンパイル・パラメータPLSQL_CODE_TYPEをNATIVEに設定して、PL/SQLネイティブ・コンパイル用に新しいデータベースを設定できます。多くのデータベース操作で使用されるOracle Database提供のPL/SQLパッケージで、パフォーマンスが改善します。
|
注意: データベース全体をNATIVEでコンパイルする場合は、システム・レベルでPLSQL_CODE_TYPEを設定することをお薦めします。 |
DBA権限を持っている場合、この項で説明するプロセスで、dbmsupgnv.sqlおよびdbmsupgin.sqlのスクリプトを使用して、既存のデータベースのすべてのPL/SQLモジュールをそれぞれNATIVEまたはINTERPRETEDに再コンパイルできます。この変換を実行する前に、「PL/SQLのネイティブ・コンパイルを使用するかどうかの判断」の内容に目を通してください。
|
注意: データベース全体をNATIVEでコンパイルする場合は、システム・レベルでPLSQL_CODE_TYPEを設定することをお薦めします。 |
ネイティブ・コンパイルへの変換で、dbmsupgnv.sqlによってTYPE仕様部をNATIVEに再コンパイルすることはできません。これらの仕様部には実行可能なコードが含まれていないためです。
パッケージ仕様部に実行可能なコードが含まれることはほとんどないため、NATIVEにコンパイルしても実行時の利点は得られません。dbmsupgnv.sqlスクリプトでTRUEコマンドライン・パラメータを使用すると、NATIVEへの再コンパイルからパッケージ仕様部を除外して、変換処理にかかる時間を節約できます。
dbmsupgin.sqlスクリプトで解釈コンパイルに変換するときは、パラメータは指定できず、PL/SQLユニットを除外することはできません。
PL/SQLテスト・ユニットをコンパイルできることを確認します。次に例を示します。
ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
アプリケーション・サービス、リスナーおよびデータベースを停止します。
Forms Process、Web Server、Reports Server、Concurrent Manager Serverなど、すべてのアプリケーション・サービスを停止します。すべてのアプリケーション・サービスを停止した後で、データベースへのすべての接続が終了したことを確認します。
データベースのTNSリスナーを停止して、新規接続が作成されないようにします。
ユーザーSYSとして、通常モードまたは即時モードでデータベースを停止します。『Oracle Database管理者ガイド』を参照してください。
コンパイル・パラメータ・ファイルでPLSQL_CODE_TYPEをNATIVEに設定します。データベースでサーバー・パラメータ・ファイルを使用している場合は、データベースを起動してからこの設定を行ってください。
PLSQL_CODE_TYPEの値は、この手順のPL/SQLユニットの変換には影響を与えません。ただし、この手順以降にコンパイルしたユニットは影響を受けるため、ここで必要なコンパイル・タイプを明示的に設定してください。
UPGRADEオプションを使用して、データベースをアップグレード・モードで起動します。SQL*PlusのSTARTUPの詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください。
次のコードを実行して、無効なPL/SQLユニットを一覧表示します。SQL SPOOL文を使用すると、問合せの出力を今後の参照のために保存しておくことができます。
-- To save the output of the query to a file: SPOOL pre_update_invalid.log SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s WHERE o.OBJECT_NAME = s.NAME AND o.STATUS='INVALID'; -- To stop spooling the output: SPOOL OFF
Oracleが提供するユニットが無効な場合は、それらのユニットを再コンパイルして有効にします。次に例を示します。
ALTER PACKAGE SYS.DBMS_OUTPUT COMPILE BODY REUSE SETTINGS;
ユニットを有効にできない場合は、今後の解決のためにスプール・ログを保存してから処理を続行します。
次の問合せを実行して、NATIVEおよびINTERPRETEDでコンパイルされるオブジェクトの数を確認します(出力を保存するには、SQLのSPOOL文を使用します)。
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS WHERE PLSQL_CODE_TYPE IS NOT NULL GROUP BY TYPE, PLSQL_CODE_TYPE ORDER BY TYPE, PLSQL_CODE_TYPE;
NULL plsql_code_typeのオブジェクトは特別な内部オブジェクトであり、無視してかまいません。
ユーザーSYSとして$ORACLE_HOME/rdbms/admin/dbmsupgnv.sqlスクリプトを実行して、すべてのPL/SQLユニットのディクショナリ表でplsql_code_type設定をNATIVEに更新します。この処理によってもユニットが無効化されます。パッケージ仕様部を除外するときは、スクリプトでTRUEを使用し、パッケージ仕様部を含めるときは、FALSEを使用します。
この更新は、データベースがUPGRADEモードの場合に実行する必要があります。スクリプトを使用すると、更新を正常に完了したり、すべての変更をロールバックすることもできます。
データベースを停止し、NORMALモードで再起動します。
問題の発生を回避するため、utlrp.sqlスクリプトを実行する前に他のセッションに接続しないことをお薦めします。このためには、次の文を実行します。
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ユーザーSYSとして$ORACLE_HOME/rdbms/admin/utlrp.sqlスクリプトを実行します。このスクリプトでは、デフォルトの並列度を使用してすべてのPL/SQLモジュールを再コンパイルします。並列度を明示的に設定する方法については、スクリプトのコメントを参照してください。
スクリプトが異常終了した場合は、utlrp.sqlスクリプトを再実行して残りの無効なPL/SQLモジュールを再コンパイルします。
コンパイルが正常に完了したら、手順5の問合せを使用して、他に無効なPL/SQLユニットがないかどうかを確認します。問合せの出力をpost_upgrade_invalid.logファイルにスプールしておくと、以前に作成したpre_upgrade_invalid.logファイルがあれば、スプールした内容と比較できます。
手順6の問合せを再実行します。dbmsupgnv.sqlで再コンパイルした場合は、除外したTYPE仕様部およびパッケージ仕様部を除くすべてのPL/SQLユニットがNATIVEであることを確認します。dbmsupgin.sqlで再コンパイルした場合は、すべてのPL/SQLユニットがINTERPRETEDであることを確認します。
データベースの制限付きセッション・モードを無効にしてから、前に停止したサービスを開始します。制限付きセッション・モードを無効にするには、次の文を使用します。
ALTER SYSTEM DISABLE RESTRICTED SESSION;
