この章では、効率的な新しいPL/SQLコードを記述する方法および既存のPL/SQLコードをスピードアップする方法について説明します。
ここでのトピック:
Oracle Databaseリリース10gより前のPL/SQLコンパイラは、パフォーマンスの向上のための変更を適用せずに、作成されたソース・コードをシステム・コードに変換していました。今回のリリースのPL/SQLでは、コードのパフォーマンスが向上するように再調整する機能を持つ最適化コンパイラが使用されます。
このオプティマイザは、デフォルトで使用可能です。まれに、オプティマイザのオーバーヘッドによって、非常に大規模なアプリケーションをコンパイルするときに時間が長くかかる場合があります。この場合、コンパイル・パラメータPLSQL_OPTIMIZE_LEVEL=1
を設定して(デフォルト値は2)、最適化レベルを低くすることができます。また、非常にまれですが、例外アクションが変更されて、例外が発生しなくなったり、予測より早く例外が発生するようになる場合もあります。PLSQL_OPTIMIZE_LEVEL=1
に設定すると、コードは再調整されません。
コンパイラが実行できる最適化の1つとして、サブプログラムのインライン化があります。サブプログラムのインライン化によって、(同じプログラム・ユニット内のサブプログラムに対する)サブプログラム・コールは、コール先サブプログラムのコピーに置き換えられます。
サブプログラムのインライン化を実行できるようにするには、PLSQL_OPTIMIZE_LEVEL
コンパイル・パラメータのデフォルト値(2)を受け入れるか、またはこのパラメータを3に設定します。PLSQL_OPTIMIZE_LEVEL=2
の場合、インライン化する各サブプログラムを指定する必要があります。PLSQL_OPTIMIZE_LEVEL=3
の場合、PL/SQLコンパイラは、指定したサブプログラム以外のサブプログラムをインライン化する機会を探ります。
特定のサブプログラムがインライン化されている場合は、ほぼ常にパフォーマンスが向上します。ただし、コンパイラは最適化プロセスの初期の段階でサブプログラムをインライン化するため、サブプログラムのインライン化によって、後の段階でのより強力な最適化が妨げられる可能性があります。
サブプログラムのインライン化によって特定のPL/SQLプログラムのパフォーマンスが低下する場合は、PL/SQL階層型プロファイラを使用して、インライン化を無効にするサブプログラムを識別します。 サブプログラムのインライン化を無効にするには、INLINE
プラグマ(「INLINEプラグマ」を参照)を使用します。
参照:
|
この章では、特に次の条件に該当する場合に有効な情報について説明します。
数学の計算を大量に実行するプログラム。 PLS_INTEGER
、BINARY_FLOAT
およびBINARY_DOUBLE
データ型について検討します。
PL/SQL問合せからコールされ、数百万回実行される可能性があるファンクション。ファンクションをできるだけ効率的にするために、すべてのパフォーマンス機能を検討します。また、各行の結果を事前に計算して問合せ時間を短縮するファンクション索引について検討することをお薦めします。
INSERT
文、UPDATE
文またはDELETE
文の処理、あるいは問合せのループに長時間を費やすプログラム。DMLを発行する場合はFORALL
文、問合せの場合はBULK
COLLECT
INTO
句およびRETURNING
BULK
COLLECT
INTO
句について検討します。
最新のPL/SQL言語機能を利用していない古いコード。Oracle Database 10gでは多くの点でパフォーマンスが改善されたため、以前のリリースのコードはすべてチューニングの対象になります。
SQLに直接渡されるだけのDDL文(CREATE
TABLE
など)を発行するのではなく、PL/SQL処理に長時間を費やすプログラム。ネイティブ・コンパイルについて検討します。PL/SQLは多くの組込みデータベース機能で使用されるため、このチューニング機能をデータベース全体に適用して、自分のコードのみでなく、多くの面でパフォーマンスを改善できます。
チューニングに取りかかる前に、現在のシステムで、個々のサブプログラムの実行時間を計測します。Oracle Database 10gのPL/SQLには、多くの自動最適化機能があるため、チューニングしなくても、パフォーマンスが改善する場合があります。
PL/SQLベースのアプリケーションのパフォーマンスが十分でない場合、その原因は通常、不適切なSQL文の記述、プログラミング慣習の不徹底、PL/SQLの基本に対する不注意、共有メモリーの間違った使用などが考えられます。
ここでのトピック:
ここでのトピック:
PL/SQLプログラムは比較的単純に見えますが、これは、ほとんどの処理がSQL文で実行されるためです。処理が低速なSQL文は、実行が低速になる主な原因となります。
SQL文によってプログラムが低速になる場合は、次の手順を試します。
適切な索引を使用していることを確認します。状況に応じて、使用する索引の種類は異なります。索引の使用方法は、問合せでの様々な表のサイズ、各問合せでのデータの配布、およびWHERE
句で使用される列によって異なる場合があります。
DBMS_STATS
パッケージのサブプログラムを使用して、すべての表について最新の統計が存在することを確認します。
次のものを使用して、実行計画およびSQL文のパフォーマンスを分析します。
EXPLAIN
PLAN
文
TKPROF
ユーティリティを持つSQLトレース機能
必要に応じて、SQL文を記述しなおします。たとえば、問合せヒントを使用すると、不要な全表スキャンなどの問題を回避できます。
これらの方法の詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
次に示すように、PL/SQL機能を利用してSQL文のパフォーマンスを改善できる場合もあります。
PL/SQLループ内でSQL文を実行している場合、INSERT
文、UPDATE
文およびDELETE
文のループのかわりに、FORALL
文を使用することを検討します。
問合せの結果セットをループしている場合、単一の操作で結果セット全体をメモリーに格納する方法として、SELECT
INTO
文のBULK COLLECT
句を使用することを検討します。
不適切に記述されたサブプログラム(低速のソートや検索機能など)によって、パフォーマンスが低下する場合があります。次に示すように、サブプログラムに対する不要なコールを回避して、コードを最適化します。
SQL問合せ内でファンクションがコールされる場合、問合せ時に表にファンクション索引を作成することで、行ごとにファンクション値をキャッシュできます。CREATE
INDEX
文には少し時間がかかる場合もありますが、問合せは非常に高速になる可能性があります。
SQL問合せ内で列がファンクションに渡される場合、問合せではその列上で通常の索引を使用できません。また、ファンクションが(非常に大きい可能性がある)表の行ごとにコールされる可能性があります。例12-1に示すように、問合せをネストして、内側の問合せで問合せ結果が少ない行数になるようにフィルタリングし、外側の問合せでファンクションが数回のみコールされるようにすることを検討します。
例12-1 問合せのネストによるパフォーマンス改善
BEGIN -- Inefficient, calls function for every row FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees) LOOP DBMS_OUTPUT.PUT_LINE(item.col_alias); END LOOP; -- Efficient, only calls function once for each distinct value. FOR item IN ( SELECT SQRT(department_id) col_alias FROM ( SELECT DISTINCT department_id FROM employees) ) LOOP DBMS_OUTPUT.PUT_LINE(item.col_alias); END LOOP; END; /
OUT
パラメータまたはIN
OUT
パラメータを使用する場合、PL/SQLは、例外が発生した場合に正しく動作するように、パフォーマンスのオーバーヘッドを追加します(OUT
パラメータの元の値が保たれるように、OUT
パラメータに値を代入した後で、未処理例外のためにサブプログラムを終了します)。
作成するプログラムにおいて、このような状況でOUT
パラメータの値を保持する必要がない場合、NOCOPY
キーワードをパラメータ宣言に追加して、パラメータをOUT
NOCOPY
またはIN
OUT
NOCOPY
と宣言できます。
この手法を使用すると、コレクション、大きいVARCHAR2
値、LOBなどの大量のデータをOUT
パラメータに渡す場合の処理を、大幅に高速化することができます。
また、この手法は、オブジェクト型のメンバー・メソッドにも適用できます。このメソッドがオブジェクト型の属性を変更する場合、すべての属性は、サブプログラムが終了するときにコピーされます。このオーバーヘッドを回避するには、PL/SQLの暗黙的な宣言であるSELF
IN
OUT
を使用するのではなく、メンバー・メソッドの最初のパラメータをSELF
IN
OUT
NOCOPY
として明示的に宣言します。オブジェクト・メソッドの設計上の考慮事項の詳細は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください。
PL/SQLアプリケーションはループの周囲に構築されることが多いため、次に示すように、ループ自体とループ内のコードの両方を最適化することが重要です。
一連のDML文を発行するために、ループ構造をFORALL
文で置換します。
結果セットをループして値を格納するかわりに、問合せでBULK
COLLECT
句を使用して、単一の操作で問合せ結果をメモリーに格納します。
結果セットを2回以上ループしたり、結果セットのループ時に他の問合せを発行する必要がある場合、元の問合せを改善して、目的の結果を正確に取得することが可能な場合があります。UNION
、INTERSECT
、MINUS
およびCONNECT
BY
問合せ演算子の使用を検討します。
問合せを他の問合せ内にネストして(副問合せという)、複数の段階でフィルタリングおよびソートを行うこともできます。たとえば、内側のWHERE
句でPL/SQLファンクションをコールすると、表の各行でファンクションが毎回コールされる可能性があります。かわりに、内側の問合せで結果セットが少ない行数になるようにフィルタリングして、外側の問合せでファンクションをコールすることができます。
PL/SQLには、REPLACE
、TRANSLATE
、SUBSTR
、INSTR
、RPAD
、LTRIM
などの高度に最適化された文字列ファンクションが多数用意されています。これらの組込みファンクションは、通常のPL/SQLよりも効率的な低レベルのコードを使用します。
正規表現の検索にPL/SQLの文字列ファンクションを使用する場合、REGEXP_SUBSTR
などの組込み正規表現ファンクションを使用することを検討します。
正規表現による検索には、SQL演算子REGEXP_LIKE
を使用します。 例6-10を参照してください。
文字列のテストまたは操作には、組込みファンクションREGEXP_INSTR
、REGEXP_REPLACE
およびREGEXP_SUBSTR
を使用します。
正規表現機能では、Linux、UNIXまたはPerlプログラミングでも使用される「.」、「*」、「^」、「$」などの文字を使用します。 多言語用のプログラミングの場合、その他の拡張機能も使用できます。たとえば、小文字の検索で、アクセント記号付きの小文字には一致しない[a-z]のかわりに、[:lower:]を使用できます。
PL/SQLは、結果が判別できた時点でただちに論理式の評価を停止します。この機能は、短絡評価と呼ばれるものです。 「短絡評価」を参照してください。
AND
またはOR
で指定された複数の条件を評価する場合、最低コストの条件を最初に配置します。たとえば、ファンクションの戻り値をテストする前に、PL/SQL変数の値を確認します。これは、PL/SQLがファンクションのコールをスキップできる場合があるためです。
PL/SQLは実行時に、異なるデータ型を自動的に変換します。たとえば、PLS_INTEGER
変数をNUMBER
変数に代入すると、両者の内部表現は異なるため、変換が実行されます。
データ型は、暗黙的な変換が最小限に抑えられるように慎重に選択してください。文字式では文字リテラル、数式では小数など、適切な型のリテラルを使用します。
変換の回数を可能なかぎり減らすには、変数の型を変更したり、異なるデータ型で表を設計しなおすことが必要となる場合があります。また、データを一度変換(INTEGER
列からPLS_INTEGER
変数への変換など)した後は、一貫してそのPL/SQL型を使用することもできます。 INTEGER
からPLS_INTEGER
データ型に変換すると、より効率の良いハードウェア算術計算が使用されるため、パフォーマンスを向上できる場合があります。 「整数の算術計算でのPLS_INTEGERまたはSIMPLE_INTEGERの使用」を参照してください。
この変数の値がNULL
の可能性がある場合、またはこの変数のオーバーフロー・チェックが必要な場合は、PLS_INTEGER
データ型を使用します。
この変数の値がNULL
になることがなく、変数のオーバーフロー・チェックが必要でない場合は、SIMPLE_INTEGER
データ型を使用します。
PLS_INTEGER
値は、INTEGER
値またはNUMBER
値より使用する記憶域が小さく、PLS_INTEGER
演算ではマシン算術計算が使用されます。 詳細は、「PLS_INTEGERおよびBINARY_INTEGERデータ型」を参照してください。
SIMPLE_INTEGER
は、PLS_INTEGER
の事前定義のサブタイプです。PLS_INTEGER
と同じ範囲を取り、NOT
NULL
制約を含んでいます。 PLS_INTEGER
との大きな違いは、オーバーフローの方法です。詳細は、「オーバーフローの方法」を参照してください。
NUMBER
型とそのサブタイプは特殊な内部形式で表され、パフォーマンスよりも移植性および任意の位取りと精度に重点を置いて設計されています。INTEGER
サブタイプの場合も、小数点以下がない浮動小数点数として扱われます。NUMBER
変数またはINTEGER
変数の演算では、ライブラリ・ルーチンへのコールが必要です。
パフォーマンスを重視するコードでは、INTEGER
、NATURAL
、NATURALN
、POSITIVE
、POSITIVEN
、SIGNTYPE
などの、制約付きのサブタイプを使用しないようにします。この型の変数を計算で使用すると、実行時に余分なチェックが必要となります。
NUMBER
型とそのサブタイプは特殊な内部形式で表され、パフォーマンスよりも移植性および任意の位取りと精度に重点を置いて設計されています。NUMBER
変数またはINTEGER
変数の演算では、ライブラリ・ルーチンへのコールが必要です。
BINARY_FLOAT
型およびBINARY_DOUBLE
型では、システム固有のマシン算術計算命令を使用することができ、科学的な処理など、大量の演算を行うアプリケーションではより適しています。また、これらの型の方が、データベースで必要な領域が少なくて済みます。
この変数の値がNULL
になることがない場合、ベース型SIMPLE_DOUBLE
またはBINARY_DOUBLE
のかわりにサブタイプSIMPLE_FLOAT
またはBINARY_FLOAT
を使用します。各サブタイプはそのベース型と同じ範囲を取り、NOT
NULL
制約を含んでいます。SIMPLE_FLOAT
およびSIMPLE_DOUBLE
値に対する算術演算はハードウェアで直接実行されるため、SIMPLE_FLOAT
およびSIMPLE_DOUBLE
を使用すると、NULLかどうかのチェックのためのオーバーヘッドが発生せず、PLSQL_CODE_TYPE='NATIVE'
の場合、BINARY_FLOAT
およびBINARY_DOUBLE
を使用するよりパフォーマンスが大幅に向上します。PLSQL_CODE_TYPE='INTERPRETED'
の場合、パフォーマンスの向上は少なくなります。
これらの型は、常に正確に小数値を表すわけではなく、NUMBER
型とは異なる四捨五入処理が行われます。これらの型は、正確さが非常に重要な財務処理コードにはあまり適していません。
式の結果がどの程度のサイズになるかが不明な場合、VARCHAR2
変数に大きいサイズを割り当てる必要がある場合があります。 VARCHAR2
変数を宣言する際に、256や1000などのやや大きいサイズを予測して指定するよりも、32000などの大きいサイズを指定する方が、メモリーを節約できます。PL/SQLの最適化機能によって、簡単にオーバーフローの問題を回避し、同時にメモリーも節約できます。VARCHAR2
型変数には、4000文字以上のサイズを指定します。PL/SQLは、変数が代入されるまで待機し、必要な量の記憶域のみを割り当てます。
パッケージ・サブプログラムを初めてコールすると、パッケージ全体が共有メモリー・プールにロードされます。パッケージ内の関連するサブプログラムに対する2度目以降のコールでは、ディスクI/Oが必要ないため実行速度が向上します。 パッケージがメモリーからエージ・アウトされた場合にそのパッケージを再度参照すると、そのパッケージは再ロードされます。
共有メモリー・プールのサイズを適切に設定すると、パフォーマンスを改善できます。 頻繁に使用するパッケージを十分に保持でき、しかもメモリーが浪費されないサイズになるように設定してください。
オラクル社が提供するパッケージDBMS_SHARED_POOL
を使用すると、頻繁にアクセスするパッケージを共有メモリー・プールに確保できます。 パッケージを確保すると、エージ・アウトされることはありません。パッケージは、プールの占有状態やパッケージへのアクセス頻度に関係なく、メモリーに残ります。
DBMS_SHARED_POOL
パッケージの詳細は、『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は、DMLや問合せなどのSQL文をSQLエンジンへ送信して実行します。SQLは、結果をPL/SQLに戻します。 バルクSQLと総称されるPL/SQL機能を使用することによって、このPL/SQLとSQL間の通信において、パフォーマンスのオーバーヘッドを最小化できます。
FORALL
文を使用すると、INSERT
文、UPDATE
文またはDELETE
文が、1文ずつではなく、バッチで送信されます。BULK
COLLECT
句を使用すると、結果がSQLからバッチで戻されます。 DML文が4つ以上のデータベース行に影響する場合は、バルクSQLでパフォーマンスを向上できます。
値をPL/SQL変数にSQL文で代入することを、バインドと呼びます。 PL/SQLバインド操作は、次のカテゴリに分類されます。
バインド・カテゴリ | このバインドが実行される場合 |
---|---|
インバインド | INSERT 文またはUPDATE 文によってPL/SQL変数またはホスト変数がデータベースに格納される場合 |
アウトバインド | INSERT 文、UPDATE 文またはDELETE 文のRETURNING 句によってPL/SQL変数またはホスト変数にデータベース値が代入される場合 |
定義 | SELECT 文またはFETCH 文によってPL/SQL変数またはホスト変数にデータベースの値が代入される場合 |
バルクSQLでは、PL/SQLコレクションを使用して、単一の操作で大量のデータの受渡しを行います。 この処理をバルク・バインドと呼びます。 コレクションにn個の要素がある場合、バルク・バインドは単一の操作で、n回分のSELECT
INTO
文、INSERT
文、UPDATE
文またはDELETE
文に相当する処理を実行できます。 バルク・バインドを使用する問合せでは、任意の数の行を戻すことができ、行ごとにFETCH
文を指定する必要はありません。
INSERT
文、UPDATE
文およびDELETE
文をスピードアップするには、LOOP
文ではなくPL/SQLのFORALL
文内にSQL文を記述します。
SELECT
INTO
文をスピードアップするには、BULK
COLLECT
句を指定します。
ここでのトピック:
キーワードFORALL
を使用すると、複数のDML文を非常に効率的に実行できます。汎用目的のFOR
ループとは異なり、1つのDML文のみを繰り返すことができます。 完全な構文と制約については、「FORALL文」を参照してください。
このSQL文では複数のコレクションを参照できますが、索引値が添字として使用される場合のみ、FORALL
によってパフォーマンスが改善されます。
通常、境界には連続した索引番号の範囲を指定します。コレクション要素を削除した後などに索引番号が連続していない場合、INDICES
OF
句またはVALUES
OF
句を使用して、実際に存在する索引値のみを反復できます。
INDICES
OF
句は、指定したコレクションの索引値全体、または下限から上限の境界内の索引値のみを反復します。
VALUES
OF
句は、PLS_INTEGER
型によって索引付けされ、要素がPLS_INTEGER
型であるコレクションを参照します。FORALL
文は、このコレクションの要素によって指定される索引値を反復します。
例12-2のFORALL
文では、3つのDELETE
文を一度にまとめてSQLエンジンに送信しています。
例12-2 ループでのDELETE文の発行
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); COMMIT; END; /
例12-3では、一部のデータをPL/SQLコレクションにロードします。次に、コレクション要素をデータベース表に2回挿入しています。1回目はFOR
ループを使用し、2回目はFORALL
文を使用します。FORALL
を使用する方が高速です。
例12-3 ループでのINSERT文の発行
CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15)); 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 := 500; t1 INTEGER; t2 INTEGER; t3 INTEGER; BEGIN FOR j IN 1..iterations LOOP -- load index-by tables pnums(j) := j; pnames(j) := 'Part No. ' || TO_CHAR(j); END LOOP; t1 := DBMS_UTILITY.get_time; FOR i IN 1..iterations LOOP -- use FOR loop INSERT INTO parts1 VALUES (pnums(i), pnames(i)); END LOOP; t2 := DBMS_UTILITY.get_time; FORALL i IN 1..iterations -- use FORALL statement INSERT INTO parts2 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; /
このブロックを実行すると、FORALL
を使用するループの方が高速であることがわかります。
例12-4に示すように、FORALL
ループの境界は、必ずしもすべての要素に適用する必要はなく、コレクションの一部に適用できます。
例12-4 コレクションの一部でのFORALLの使用
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 -- use only part of varray DELETE FROM employees_temp WHERE department_id = depts(j); COMMIT; END; /
FORALL
文でコレクションを使用する前に、コレクションから一部の要素を削除する必要がある場合があります。INDICES
OF
句は、残りの要素のみを反復して、疎コレクションを処理します。
元のコレクションを変更せずに、一部の要素のみを処理したり、要素を異なる順序で処理したり、または一部の要素を複数回処理する場合もあります。VALUES
OF
句を使用すると、要素全体を新しいコレクションにコピーする(その結果、場合によっては大量のメモリーが消費される)かわりに、元のコレクションの要素へのポインタとして機能する要素を持つ単純なコレクションを設定できます。
例12-5では、任意のデータ(表名のセット)を保持するコレクションを作成します。要素の一部を削除すると、デフォルトのFORALL
文では動作しない疎コレクションになります。プログラムは、INDICES
OF
句を指定したFORALL
文を使用して、データを表に挿入します。次に、元のコレクションの特定の要素を指す2つのコレクションを新しく設定します。VALUES
OF
句を指定したFORALL
文を使用して、名前の各セットを別のデータベース表に格納します。
例12-5 連続していない索引値でのFORALLの使用
-- Create empty tables to hold order details CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2)); CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0; CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0; DECLARE -- Collections for set of customer names & order amounts: SUBTYPE cust_name IS valid_orders.cust_name%TYPE; TYPE cust_typ IS TABLe OF cust_name; cust_tab cust_typ; SUBTYPE order_amount IS valid_orders.amount%TYPE; TYPE amount_typ IS TABLE OF NUMBER; amount_tab amount_typ; -- Collections to point into CUST_TAB collection. TYPE index_pointer_t IS TABLE OF PLS_INTEGER; big_order_tab index_pointer_t := index_pointer_t(); rejected_order_tab index_pointer_t := index_pointer_t(); PROCEDURE setup_data IS BEGIN -- Set up sample order data, -- including some invalid orders and some 'big' orders. cust_tab := cust_typ('Company1','Company2', 'Company3','Company4','Company5'); amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL); END; BEGIN setup_data(); 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 (where amount is null or 0). 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; DBMS_OUTPUT.PUT_LINE ('--- 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; -- Because subscripts of collections are not consecutive, -- use FORALL...INDICES OF to iterate through actual subscripts, -- rather than 1..COUNT FORALL i IN INDICES OF cust_tab INSERT INTO valid_orders(cust_name, amount) VALUES(cust_tab(i), amount_tab(i)); -- Now process the order data differently -- Extract 2 subsets and store each subset in a different table -- Initialize the CUST_TAB and AMOUNT_TAB collections again. setup_data(); FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN -- Add a new element to this collection rejected_order_tab.EXTEND; -- Record the subscript from the original collection rejected_order_tab(rejected_order_tab.LAST) := i; END IF; IF amount_tab(i) > 2000 THEN -- Add a new element to this collection big_order_tab.EXTEND; -- Record the subscript from the original collection big_order_tab(big_order_tab.LAST) := i; END IF; END LOOP; -- Now it's easy to run one DML statement -- on one subset of elements, -- and another DML statement on a different subset. FORALL i IN VALUES OF rejected_order_tab INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i)); FORALL i IN VALUES OF big_order_tab INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i)); COMMIT; END; / -- Verify that the correct order details were stored SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders; SELECT cust_name "Customer", amount "Big order amount" FROM big_orders; SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;
ここでのトピック:
FORALL
文では、SQL文の実行によって未処理例外が発生した場合、前回の実行中に行われたすべてのデータベース変更はロールバックされます。ただし、呼び出された例外が捕捉され処理されると、変更は、各SQL文の実行の前にマークされた暗黙的なセーブポイントまでロールバックされます。前の実行の間に行われた変更は、ロールバックされません。たとえば、例12-6に示すように、部門番号と肩書きを格納するデータベース表を作成するとします。次に、肩書きを、より長い肩書きに変更します。この新しい値が長すぎてその列では使用できないため、2番目のUPDATE
は失敗します。 この例外に対する処理は行われるため、最初のUPDATE
はロールバックされず、変更はコミットされます。
例12-6 FORALLでのロールバックの使用
CREATE TABLE emp_temp (deptno NUMBER(2), job VARCHAR2(18)); DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10, 20, 30); BEGIN INSERT INTO emp_temp VALUES(10, 'Clerk'); -- Lengthening this job title causes an exception INSERT INTO emp_temp VALUES(20, 'Bookkeeper'); INSERT INTO emp_temp VALUES(30, 'Analyst'); COMMIT; FORALL j IN depts.FIRST..depts.LAST -- Run 3 UPDATE statements. UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j); -- raises a "value too large" exception EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Problem in the FORALL statement.'); COMMIT; -- Commit results of successful updates. END; /
カーソル属性のSQL%FOUND
、SQL%ISOPEN
、SQL%NOTFOUND
およびSQL%ROWCOUNT
は、直前に実行されたDML文についての役に立つ情報を戻します。 カーソル属性の詳細は、「SQLカーソル(暗黙)」を参照してください。
SQL
カーソルは、FORALL
で使用するための複合属性%BULK_ROWCOUNT
を持ちます。この属性は、結合配列のように機能します。SQL%BULK_ROWCOUNT(i)
には、INSERT
文、UPDATE
文またはDELETE
文のi番目の実行によって処理された行数が格納されます(例12-7を参照)。
例12-7 FORALL文での%BULK_ROWCOUNTの使用
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); -- How many rows were affected by each DELETE statement? FOR i IN depts.FIRST..depts.LAST LOOP DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' || SQL%BULK_ROWCOUNT(i) || ' rows.'); END LOOP; END; /
FORALL
文と%BULK_ROWCOUNT
属性は同じ添字を使用します。たとえば、FORALL
が5から10の範囲を使用した場合は、%BULK_ROWCOUNT
でも同じ範囲が使用されます。FORALL
文でINDICES OF
句を使用して疎コレクションを処理する場合、%BULK_ROWCOUNT
は、対応する疎の添字を持ちます。FORALL
文でVALUES OF
句を使用して要素のサブセットを処理する場合、%BULK_ROWCOUNT
は、索引コレクションの要素の値に対応する添字を持ちます。索引コレクションの要素が重複しているために一部のDML文が同じ添字を使用して複数回発行される場合、%BULK_ROWCOUNT
の対応する要素は、その添字を使用するDML文によって影響を受けたすべての行の合計です。
典型的な挿入操作は1行にのみ影響するため、通常、挿入の場合の%BULK_ROWCOUNT
は1です。INSERT
SELECT
構造の場合は、%BULK_ROWCOUNT
が1より大きくなることがあります。たとえば、例12-8のFORALL
文は、反復のたびに任意の数の行を挿入します。それぞれの反復後に、%BULK_ROWCOUNT
は挿入された行数を戻します。
例12-8 FORALLで%BULK_ROWCOUNTを指定した場合の行カウント
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 SELECT employee_id, department_id FROM employees WHERE department_id = deptnums(i); 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; /
FORALL
文を実行した後で、スカラー属性の%FOUND
、%NOTFOUND
および%ROWCOUNT
も使用できます。たとえば、%ROWCOUNT
は、SQL文のすべての実行によって処理された行の総数を戻します。
%FOUND
と%NOTFOUND
は、SQL文の最後の実行のみを参照します。 %BULK_ROWCOUNT
を使用すると、個々の実行に対する値を推論できます。 たとえば、%BULK_ROWCOUNT(i)
がゼロの場合、%FOUND
と%NOTFOUND
はそれぞれ、FALSE
およびTRUE
になります。
PL/SQLには、FORALL
文の実行中に呼び出される例外を処理するメカニズムが用意されています。このメカニズムによって、バルク・バインド操作では、例外に関する情報を保存して処理を継続できます。
エラーが発生した場合もバルク・バインドを完了させるには、FORALL
文にキーワードSAVE
EXCEPTIONS
を(境界の後、DML文の前に)追加します。バルク操作中に発生した例外を追跡する場合は、例外ハンドラを用意します。
例12-9に、DML操作でエラーが発生しても停止せずに、多数のDML操作を実行する方法の例を示します。 この例では、EXCEPTION_INIT
を使用して、DML_ERRORS
例外を事前定義のエラーORA-24381に関連付けます。ORA-24381は、バルク操作後に例外が捕捉され、保存された場合に発生します。
実行中に呼び出されたすべての例外は、レコードのコレクションを格納するカーソル属性%BULK_EXCEPTIONS
に保存されます。各レコードには次の2つのフィールドがあります。
%BULK_EXCEPTIONS(i)
.ERROR_INDEX
には、例外が呼び出されたときに実行中だったFORALL
文の反復が保持されます。
%BULK_EXCEPTIONS(i
).ERROR_CODE
には、対応するOracle Databaseエラー・コードが保持されます。
%BULK_EXCEPTIONS
によって格納された値は常に、直前に実行されたFORALL
文を参照します。例外の数は、%BULK_EXCEPTIONS
.COUNT
に保存されます。添字の範囲は1からCOUNT
です。
個々のエラー・メッセージは、置換される引数も含めて保存されませんが、例12-9に示すように、ERROR_CODE
をSQLERRM
とともに使用すると、エラー・メッセージの本文を検索できます。
例外が発生した反復で使用されたコレクション要素を特定するために、処理を逆にたどる必要がある場合があります。たとえば、INDICES
OF
句を使用して疎コレクションを処理する場合、要素を1つずつ確認して、%BULK_EXCEPTIONS(i)
.ERROR_INDEX
に対応する要素を検出する必要があります。VALUES
OF
句を使用して要素のサブセットを処理する場合、索引コレクション内で添字が%BULK_EXCEPTIONS(i
).ERROR_INDEX
に一致する要素を検出し、次にその要素の値を添字として使用して、元のコレクション内の誤った要素を検出する必要があります。
キーワードSAVE
EXCEPTIONS
を省略すると、例外が呼び出された時点でFORALL
文の実行が停止します。その場合、SQL%BULK_EXCEPTIONS
.COUNT
は1を戻し、SQL%BULK_EXCEPTIONS
にはレコードが1つのみ含まれます。実行中に例外が呼び出されなければ、SQL%BULK_EXCEPTIONS
.COUNT
は0(ゼロ)を戻します。
例12-9 例外が発生しても継続するバルク操作
-- Temporary table for this example: CREATE TABLE emp_temp AS SELECT * FROM employees; DECLARE TYPE empid_tab IS TABLE OF employees.employee_id%TYPE; emp_sr empid_tab; -- Exception handler for ORA-24381: errors NUMBER; dml_errors EXCEPTION; PRAGMA EXCEPTION_INIT(dml_errors, -24381); BEGIN SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp WHERE hire_date < '30-DEC-94'; -- Add '_SR' to job_id of most senior employees: FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS UPDATE emp_temp SET job_id = job_id || '_SR' WHERE emp_sr(i) = emp_temp.employee_id; -- If errors occurred during FORALL SAVE EXCEPTIONS, -- a single exception is raised when the statement completes. EXCEPTION -- Figure out what failed and why WHEN dml_errors THEN errors := SQL%BULK_EXCEPTIONS.COUNT; DBMS_OUTPUT.PUT_LINE ('Number of statements that failed: ' || errors); FOR i IN 1..errors LOOP DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '|| 'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); DBMS_OUTPUT.PUT_LINE('Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); END LOOP; END; / DROP TABLE emp_temp;
この例の出力は次のようになります。
Number of statements that failed: 2 Error #1 occurred during iteration #7 Error message is ORA-12899: value too large for column Error #2 occurred during iteration #13 Error message is ORA-12899: value too large for column
例12-9のPL/SQLでは、更新後の値が大きすぎてjob_id
列に挿入できないため、事前定義の例外が発生します。FORALL
文の後、SQL%BULK_EXCEPTIONS
.COUNT
が2を戻し、SQL%BULK_EXCEPTIONS
の内容が(7,12899)および(13,12899)になりました。
Oracle Databaseエラー・メッセージを(コードを含めて)取得するために、SQL%BULK_EXCEPTIONS(i)
.ERROR_CODE
の値を無効にして、エラー・レポート・ファンクションSQLERRM
に渡しています。このファンクションでは負の数値が予測されています。
問合せでBULK
COLLECT
句を使用すると、結果セットを非常に効率的に取り出すことができます。各行をループするかわりに、単一の操作で、結果を1つ以上のコレクションに格納できます。 BULK
COLLECT
句は、SELECT
INTO
文、FETCH
INTO
文およびRETURNING
INTO
句で使用できます。
BULK
COLLECT
句では、INTO
リスト内のすべての変数はコレクションである必要があります。表の列には、スカラー値または複合値(オブジェクト型を含む)を格納できます。
例12-10では、ネストした表に、データベースの2つの列全体をロードします。
例12-10 BULK COLLECTによる問合せ結果の取得
DECLARE TYPE NumTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employees.last_name%TYPE; enums NumTab; -- No need to initialize collections names NameTab; -- Values will be filled by SELECT INTO PROCEDURE print_results IS BEGIN IF enums.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No results!'); ELSE DBMS_OUTPUT.PUT_LINE('Results:'); FOR i IN enums.FIRST .. enums.LAST LOOP DBMS_OUTPUT.PUT_LINE (' Employee #' || enums(i) || ': ' names(i)); END LOOP; END IF; END; BEGIN -- Retrieve data for employees with Ids greater than 1000 SELECT employee_id, last_name BULK COLLECT INTO enums, names FROM employees WHERE employee_id > 1000; -- Data was brought into memory by BULK COLLECT -- No need to FETCH each row from result set print_results(); -- Retrieve approximately 20% of all rows SELECT employee_id, last_name BULK COLLECT INTO enums, names FROM employees SAMPLE (20); print_results(); END; /
コレクションは自動的に初期化されます。ネストした表および結合配列は、必要な数の要素を保持できるように拡張されます。VARRAYを使用する場合、すべての戻り値は、宣言したVARRAYのサイズに格納できる必要があります。要素は、索引1から、既存の要素を上書きしながら挿入されます。
BULK
COLLECT
INTO
句の処理はFETCH
ループに類似しており、問合せに一致する行がない場合にNO_DATA_FOUND
例外は発生しません。 例12-11に示すように、処理後のネストした表またはVARRAYがNULLかどうか、または処理後の結合配列に要素が含まれているかどうかを確認する必要があります。
結果のコレクションが無制限に拡大するのを防ぐため、LIMIT
句または擬似列ROWNUM
を使用して、処理される行の数を制限できます。また、SAMPLE
句を使用して、行のランダムなサンプルを取り出すこともできます。
例12-11 擬似列ROWNUMによる問合せ結果の制限
DECLARE TYPE SalList IS TABLE OF employees.salary%TYPE; sals SalList; BEGIN -- Limit number of rows to 50 SELECT salary BULK COLLECT INTO sals FROM employees WHERE ROWNUM <= 50; -- Retrieve ~10% rows from table SELECT salary BULK COLLECT INTO sals FROM employees SAMPLE (10); END; /
次の項に示すように、指定した数の行をカーソルから一度にフェッチすることで、非常にサイズが大きい結果セットを処理できます。
ここでのトピック:
例12-12に示すように、1つのカーソルから1つ以上のコレクションにフェッチできます。
例12-12 カーソルから1つ以上のコレクションへのバルク・フェッチ
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; 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('Results: '); 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 at once ---'); 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; /
例12-13に、1つのカーソルから複数レコードのコレクションにフェッチする方法を示します。
バルクFETCH
文でのみ使用可能なオプションのLIMIT
句を使用すると、データベースからフェッチされる行の数を制限できます。 例12-14では、ループが繰り返されるたびに、FETCH
文によって10行(またはそれ以下)が索引付き表empids
にフェッチされます。前の値は上書きされます。ループから抜けるタイミングを判断するために、empids
.COUNT
を使用しています。
例12-14 LIMITによるBULK COLLECTの行数の制御
DECLARE TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; CURSOR c1 IS SELECT employee_id FROM employees WHERE department_id = 80; empids numtab; rows PLS_INTEGER := 10; BEGIN OPEN c1; -- Fetch 10 rows or less in each iteration LOOP FETCH c1 BULK COLLECT INTO empids LIMIT rows; EXIT WHEN empids.COUNT = 0; -- EXIT WHEN c1%NOTFOUND; -- incorrect, can omit some data DBMS_OUTPUT.PUT_LINE ('------- Results from Each Bulk Fetch --------'); FOR i IN 1..empids.COUNT LOOP DBMS_OUTPUT.PUT_LINE( 'Employee Id: ' || empids(i)); END LOOP; END LOOP; CLOSE c1; END; /
BULK
COLLECT
句は、INSERT
文、UPDATE
文またはDELETE
文のRETURNING
INTO
句で使用できます。
例12-15 RETURNING INTO句でのBULK COLLECTの使用
CREATE TABLE emp_temp AS SELECT * FROM employees; 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 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; /
FORALL
文とBULK
COLLECT
句を組み合せることができます。出力コレクションは、FORALL
文が反復されるとともに構築されます。
例12-16では、削除対象の各行のemployee_id
値をコレクションe_ids
に格納しています。コレクションdepts
には3つの要素が存在するため、FORALL
文は3回反復します。 FORALL
文によって発行される各DELETE
で5行ずつ削除される場合、削除された行の値を格納するコレクションe_ids
には、文が完了すると15の要素が保持されます。
例12-16 BULK COLLECTとFORALLの併用
CREATE TABLE emp_temp AS SELECT * FROM employees; DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10,20,30); TYPE enum_t IS TABLE OF employees.employee_id%TYPE; TYPE dept_t IS TABLE OF employees.department_id%TYPE; e_ids enum_t; 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; /
各実行によって戻された列の値は、前に戻された値に追加されます。FORALL
文のかわりにFOR
ループを使用する場合、戻り値のセットは、各DELETE
文によって上書きされます。
FORALL
文では、SELECT
BULK
COLLECT
文は使用できません。
クライアント側のプログラムは、無名PL/SQLブロックを使用してホスト配列をバルク・バインド入出力できます。これは、コレクションをデータベース・サーバーとの間でやり取りするのに最も効率的な方法です。
ホスト配列はOCIやPro*Cプログラムなどのホスト環境で宣言され、PL/SQLコレクションと区別するためのコロンを接頭辞として付ける必要があります。次の例では、DELETE
文に入力ホスト配列が使用されています。実行時に、無名PL/SQLブロックがデータベース・サーバーに送信されて、実行されます。
DECLARE BEGIN -- Assume that values were assigned to host array -- and host variables in host environment FORALL i IN :lower..:upper DELETE FROM employees WHERE department_id = :depts(i); COMMIT; END;
次の書式の文について説明します。
SELECT column BULK COLLECT INTO collection FROM table ...
column
およびcollection
は、それぞれIN
およびOUT
NOCOPY
サブプログラム・パラメータに類似しており、PL/SQLによって参照方式で渡されます。 参照方式で渡されるサブプログラム・パラメータと同様に、エイリアシングによって予期しない結果が発生する場合があります。
例12-17では、コレクションnumbers1
から特定の値を選択し、その値を同じコレクションに格納することを意図しています。 ところが、numbers1
のすべての要素が削除されるという予期しない結果が発生します。 この問題の回避策は、例12-18および例12-19を参照してください。
例12-17 予期しない結果が発生するSELECT BULK COLLECT INTO文
SQL> CREATE OR REPLACE TYPE numbers_type IS 2 TABLE OF INTEGER 3 / Type created. SQL> CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS 2 numbers1 numbers_type := numbers_type(1,2,3,4,5); 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('Before SELECT statement'); 5 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 6 7 FOR j IN 1..numbers1.COUNT() LOOP 8 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 9 END LOOP; 10 11 --Self-selecting BULK COLLECT INTO clause: 12 13 SELECT a.COLUMN_VALUE 14 BULK COLLECT INTO numbers1 15 FROM TABLE(numbers1) a 16 WHERE a.COLUMN_VALUE > p.i 17 ORDER BY a.COLUMN_VALUE; 18 19 DBMS_OUTPUT.PUT_LINE('After SELECT statement'); 20 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 21 END p; 22 / Procedure created. SQL> BEGIN 2 p(2); 3 END; 4 / 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. SQL> BEGIN 2 p(10); 3 END; 4 / 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 SQL>
例12-18では、カーソルを使用して例12-17で意図されていた結果を実現します。
例12-18 カーソルを使用した例12-17の回避策
SQL> CREATE OR REPLACE TYPE numbers_type IS 2 TABLE OF INTEGER 3 / Type created. SQL> CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS 2 numbers1 numbers_type := numbers_type(1,2,3,4,5); 3 4 CURSOR c IS 5 SELECT a.COLUMN_VALUE 6 FROM TABLE(numbers1) a 7 WHERE a.COLUMN_VALUE > p.i 8 ORDER BY a.COLUMN_VALUE; 9 BEGIN 10 DBMS_OUTPUT.PUT_LINE('Before FETCH statement'); 11 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 12 13 FOR j IN 1..numbers1.COUNT() LOOP 14 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 15 END LOOP; 16 17 OPEN c; 18 FETCH c BULK COLLECT INTO numbers1; 19 CLOSE c; 20 21 DBMS_OUTPUT.PUT_LINE('After FETCH statement'); 22 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 23 24 IF numbers1.COUNT() > 0 THEN 25 FOR j IN 1..numbers1.COUNT() LOOP 26 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 27 END LOOP; 28 END IF; 29 END p; 30 / Procedure created. SQL> BEGIN 2 p(2); 3 END; 4 / 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 PL/SQL procedure successfully completed. SQL> BEGIN 2 p(10); 3 END; 4 / 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 PL/SQL procedure successfully completed. SQL> DROP TYPE numbers_type; Type dropped. SQL> DROP PROCEDURE p; Procedure dropped. SQL>
例12-19では、コレクションnumbers1
から特定の値を選択し、その値を別のコレクションnumbers2
に格納します。 例12-19の処理速度は例12-18の場合より速くなります。
例12-19 2番目のコレクションを使用した例12-17の回避策
SQL> CREATE OR REPLACE TYPE numbers_type IS 2 TABLE OF INTEGER 3 / Type created. SQL> CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS 2 numbers1 numbers_type := numbers_type(1,2,3,4,5); 3 numbers2 numbers_type := numbers_type(0,0,0,0,0); 4 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE('Before SELECT statement'); 7 8 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 9 10 FOR j IN 1..numbers1.COUNT() LOOP 11 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 12 END LOOP; 13 14 DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT()); 15 16 FOR j IN 1..numbers2.COUNT() LOOP 17 DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j)); 18 END LOOP; 19 20 SELECT a.COLUMN_VALUE 21 BULK COLLECT INTO numbers2 -- numbers2 appears here 22 FROM TABLE(numbers1) a -- numbers1 appears here 23 WHERE a.COLUMN_VALUE > p.i 24 ORDER BY a.COLUMN_VALUE; 25 26 DBMS_OUTPUT.PUT_LINE('After SELECT statement'); 27 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 28 29 IF numbers1.COUNT() > 0 THEN 30 FOR j IN 1..numbers1.COUNT() LOOP 31 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 32 END LOOP; 33 END IF; 34 35 DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT()); 36 37 IF numbers2.COUNT() > 0 THEN 38 FOR j IN 1..numbers2.COUNT() LOOP 39 DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j)); 40 END LOOP; 41 END IF; 42 END p; 43 / Procedure created. SQL> BEGIN 2 p(2); 3 END; 4 / 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. SQL> BEGIN 2 p(10); 3 END; 4 / 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 PL/SQL procedure successfully completed. SQL>
BINARY_FLOAT
およびBINARY_DOUBLE
データ型を使用すると、浮動小数点計算を伴う科学アプリケーションなど、大量の演算を行う実用的なPL/SQLプログラムを記述できます。 これらのデータ型は、多くのハードウェア・システム上でシステム固有の浮動小数点型とほぼ同様に動作し、IEEE-754浮動小数点標準で示されるセマンティックを持ちます。
これらのデータ型で小数データを表す方法は、財務処理アプリケーションにはあまり適していません。財務処理アプリケーションの場合、小数部分が正確に表されることの方が、単にパフォーマンスが向上することよりも重要です。
PLS_INTEGER
データ型はPL/SQL専用のデータ型で、整数の算術計算の場合、SQLデータ型のNUMBER
またはINTEGER
より効率的です。PLS_INTEGER
を使用して、整数の算術計算用に純粋なPL/SQLコードを記述したり、PL/SQLで操作できるようにNUMBER
またはINTEGER
値をPLS_INTEGER
に変換できます。
パッケージ内で、異なる数値パラメータを受け入れるための、オーバーロードされるサブプログラムを記述できます。数学ルーチンは、パラメータ(BINARY_FLOAT
、BINARY_DOUBLE
、NUMBER
およびPLS_INTEGER
)の種類に合わせて最適化して、不要な変換を回避できます。
SQRT
、SIN
、COS
などの組込み数学ファンクションには、BINARY_FLOAT
パラメータおよびBINARY_DOUBLE
パラメータを受け入れる、オーバーロードされる高速なファンクションがすでに用意されています。BINARY_FLOATおよびBINARY_DOUBLE型の変数をこのようなファンクションに渡したり、このようなファンクションに式を渡すときにTO_BINARY_FLOAT
またはTO_BINARY_DOUBLE
ファンクションをコールすることによって、計算集中型コードをスピードアップできます。
たとえば汎用目的のレポート・ライターなど、プログラムによっては、文の正確なテキストが実行時まで判明しない場合、様々なSQL文を構築および処理する必要があります。多くの場合、このような文は実行ごとに変わります。このような文は動的SQL文と呼ばれます。
以前は、動的SQL文を実行するには、提供されているパッケージDBMS_SQL
を使用する必要がありました。現在、PL/SQL内では、どの種類の動的SQL文でも、システム固有の動的SQLと呼ばれるインタフェースを使用して実行できます。これに関連する主なPL/SQL機能は、EXECUTE
IMMEDIATE
文およびカーソル変数(REF
CURSOR
ともいう)です。
システム固有の動的SQLコードは、DBMS_SQL
パッケージをコールする場合よりもサイズが小さくなり、処理速度も高速になります。次の例では、カーソル変数を宣言し、そのカーソル変数を動的SELECT
文に関連付けます。
DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; v_ename VARCHAR2(15); v_sal NUMBER := 1000; table_name VARCHAR2(30) := 'employees'; BEGIN OPEN emp_cv FOR 'SELECT last_name, salary FROM ' || table_name || ' WHERE salary > :s' USING v_sal; CLOSE emp_cv; END; /
詳細は、第7章「動的SQLの使用」を参照してください。
デフォルトでは、OUT
パラメータとIN
OUT
パラメータは値によって渡されます。すべてのIN
OUT
パラメータの値は、サブプログラムの実行前にコピーされます。サブプログラムの実行中は、一時変数に出力パラメータ値が保持されます。サブプログラムが正常に終了した場合、この値は実パラメータにコピーされます。サブプログラムが未処理例外で終了した場合、元のパラメータは変更されません。
パラメータが、コレクション、レコード、オブジェクト型のインスタンスなどの大規模なデータ構造を表す場合、このコピー作業によって実行速度が遅くなり、メモリーが消費されます。特に、このオーバーヘッドは、オブジェクト・メソッドに対する各コールで発生します。メソッドが正常に終了した場合のみメソッドによる変更が適用されるように、一時コピーがすべての属性に対して作成されます。
このオーバーヘッドを回避するには、NOCOPY
ヒントを指定します。これによって、PL/SQLコンパイラはOUT
およびIN
OUT
パラメータを参照によって渡すことができます。サブプログラムが正常に終了した場合、アクションは通常の場合と同じです。サブプログラムが例外によって終了した場合も、OUT
およびIN
OUT
パラメータ(またはオブジェクト属性)の値が変更されることがあります。この手法を使用する場合、サブプログラムですべての例外が処理されるようにします。
次の例では、IN
OUT
パラメータv_staff
を参照によって渡すように、コンパイラに指示します。これによって、エントリのVARRAYをサブプログラムにコピーしてサブプログラムが終了することを回避できます。
DECLARE TYPE Staff IS VARRAY(200) OF Employee; PROCEDURE reorganize (v_staff IN OUT NOCOPY Staff) IS ...
例12-20では、ローカルのネストした表に25,000レコードがロードされます。この表は、何も実行しない2つのローカル・プロシージャに渡されます。サブプログラムへのコールでNOCOPY
を使用する方が、時間がかかりません。
例12-20 パラメータでの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; -- Copy element 1 into 2..50000 emp_tab.EXTEND(49999, 1); get_time(t1); -- Pass IN OUT parameter do_nothing1(emp_tab); get_time(t2); -- Pass IN OUT NOCOPY parameter do_nothing2(emp_tab); 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; /
NOCOPYヒントの制限
NOCOPY
を使用することによって、パラメータのエイリアシングの可能性が高くなります。 詳細は、「PL/SQLサブプログラムのパラメータのエイリアシングの理解」を参照してください。
NOCOPY
は、ディレクティブではなく、ヒントです。次の場合には、PL/SQLコンパイラはNOCOPY
ヒントを無視して、値によってパラメータを渡します。エラーは生成されません。
実パラメータが結合配列の要素である場合。この制限は、パラメータが結合配列全体の場合は適用されません。
実パラメータに、位取りやNOT
NULL
などの制約がある場合。この制限は、サイズ制約付きの文字列には適用されません。この制限は、制約付きの要素またはコンポジット型の属性には拡張されません。
実パラメータと仮パラメータがレコードであり、いずれかまたは両方のレコードが%ROWTYPE
または%TYPE
を使用して宣言されており、レコード内の対応するフィールドの制約が異なる場合。
実パラメータと仮パラメータがレコードであり、実パラメータはカーソルFOR
ループの索引として(暗黙的に)宣言されており、レコード内の対応するフィールドの制約が異なる場合。
実パラメータを渡すために、暗黙的なデータ型の変換が必要となる場合。
サブプログラムが、データベース・リンクによって、または外部サブプログラムとしてコールされる場合。
通常、PL/SQLユニットをコンパイルして、システム固有のコード(プロセッサに依存するシステム・コード)にすると、PL/SQLユニットをスピードアップできます。システム固有のコードは、SYSTEM表領域に格納されます。
オラクル社が提供するユニットを含め、すべてのタイプのPL/SQLユニットをネイティブ・コンパイルできます。
ネイティブ・コンパイルされたプログラム・ユニットは、共有サーバー構成(以前のマルチスレッド・サーバー)やOracle Real Application Clusters(Oracle RAC)などのすべてのサーバー環境で動作します。
ほとんどのプラットフォームで、PL/SQLネイティブ・コンパイルに特別な設定またはメンテナンスは必要ありません。 一部のプラットフォームでは、DBAによるオプションの構成が必要な場合もあります。
参照:
|
PL/SQLのネイティブ・コンパイルを有効にしてパフォーマンスが改善される度合いは、テストによって確認できます。
PL/SQLのネイティブ・コンパイルを使用するとデータベース操作で大幅にパフォーマンスが改善されると判断した場合、ネイティブ・モードでデータベース全体をコンパイルすることをお薦めします。これを行うには、DBA権限が必要です。これによって、独自のコードとすべての組込み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文はシステム固有のコードにコンパイルされ、SYSTEM表領域に格納されます。 システム固有のコードは、実行時に解釈する必要がないため、高速で実行されます。
ネイティブ・コンパイルはPL/SQL文にのみ適用されるため、SQL文をコールするのみのPL/SQLユニットの場合、ネイティブ・コンパイルしても高速に実行されない可能性がありますが、対応する解釈済コードより低速になることはありません。コンパイルされたコードと解釈済コードは同じライブラリをコールするため、アクションは同じです。
ネイティブ・コンパイルされたPL/SQLユニットを初めて実行すると、このユニットは、SYSTEM表領域から共有メモリーにフェッチされます。プログラム・ユニットをコールするセッションの数に関係なく、共有メモリーにはそのコピーが1つのみ含まれています。プログラム・ユニットが使用されていない場合、そのプログラム・ユニットが使用している共有メモリーを解放してメモリー・ロードを軽減できます。
ネイティブ・コンパイルされたサブプログラムと解釈済サブプログラムは相互にコールし合うことができます。
PLSQLのネイティブ・コンパイルは、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ネイティブ・コンパイル用に新しいデータベースを設定できます。多くのデータベース操作で使用されるすべての組込み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
文を使用すると、問合せの出力を今後の参照のために保存しておくことができます。
REM 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'; REM 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;
この項では、パイプライン・テーブル・ファンクションと呼ばれる特殊なファンクションを連鎖する方法について説明します。テーブル・ファンクションは、データ・ウェアハウスなどで複数の変換をデータに適用する場合に使用します。
注意: パイプライン・テーブル・ファンクションは、データベース・リンクを介して実行することはできません。 パイプライン・テーブル・ファンクションの戻り型がユーザー定義のSQL型であり、単一データベース内でのみ使用可能であるためです(『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照)。 パイプライン・テーブル・ファンクションの戻り型がPL/SQL型のように見える場合でも、そのPL/SQL型は実際にはデータベースによって対応するユーザー定義のSQL型に変換されます。 |
ここでのトピック:
パイプライン・テーブル・ファンクションを使用すると、PL/SQLで行ソースをプログラムできます。 テーブル・ファンクションは、SQLのSELECT
文のFROM
リストで表演算子のオペランドとして起動します。 テーブル・ファンクションは、SELECT
リスト項目として起動することもできます。この場合は、表演算子を使用しません。
テーブル・ファンクションは、入力として行のコレクションを使用することができます。入力コレクション・パラメータには、コレクション型(VARRAY
やPL/SQL表など)またはREF
CURSOR
を使用できます。
テーブル・ファンクションの実行はパラレル化でき、戻される行は中間のステージングなしで次のプロセスに直接送ることができます。 テーブル・ファンクションから戻されるコレクションの行は、パイプライン化することもできます。つまり、テーブル・ファンクションの入力の処理がすべて完了してからバッチで戻されるのではなく、生成された時点で反復的に戻されます。
注意: テーブル・ファンクションから戻されるコレクションの行をパイプライン化すると、そのパイプライン・ファンクションは常にデータの現在の状態を参照します。 コレクションでカーソルをオープンした後、コレクションのデータが変更されると、その変更はカーソルに反映されます。 PL/SQL変数は単一セッション内でのみ有効であり、トランザクション対応ではありません。 このため、表データへの適用性がよく知られている読取り一貫性の概念は、PL/SQLコレクション変数には適用されません。 |
テーブル・ファンクションのストリーム、パイプラインおよびパラレル実行によって、次のようにパフォーマンスを改善できます。
マルチスレッドが可能になり、テーブル・ファンクションを同時に実行します。
プロセス間の中間的なステージングを排除します。
問合せの応答時間が短縮されます。パイプラインでないテーブル・ファンクションの場合は、問合せで1つの結果行を戻す前に、テーブル・ファンクションから戻されるコレクション全体を組み立てて、サーバーに戻す必要があります。パイプライン化によって、行を、生成時に反復的に戻すことができます。 また、オブジェクト・キャッシュでコレクション全体をマテリアライズする必要がないため、テーブル・ファンクションのメモリー消費量も減少します。
コレクション全体が表やメモリーにステージングされるまで待ってから、コレクション全体を戻すのではなく、各行の生成時にテーブル・ファンクションから戻されるコレクションの結果行を反復的に提供します。
パイプライン・テーブル・ファンクションを宣言するには、PIPELINED
キーワードを指定します。パイプライン・ファンクションは、CREATE
FUNCTION
を使用してスキーマ・レベルで定義することも、パッケージに定義することもできます。PIPELINED
キーワードは、ファンクションが行を反復的に戻すことを示します。パイプライン・テーブル・ファンクションの戻り型は、ネストした表やVARRAYなど、サポートされているコレクション型である必要があります。このコレクション型は、スキーマ・レベルまたはパッケージ内で宣言できます。ファンクション内では、コレクション型の個々の要素を戻します。 コレクション型の要素は、NUMBER
やVARCHAR2
など、サポートされているSQLデータ型である必要があります。 パイプライン・ファンクションでは、PLS_INTEGER
やBOOLEAN
などのPL/SQLデータ型はコレクション要素としてサポートされません。
例12-21に、パイプライン・テーブル・ファンクションの結果をPL/SQLコレクション変数に代入し、そのファンクションをSELECT
文で使用する方法を示します。
例12-21 テーブル・ファンクションの結果の代入
CREATE 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; END pkg1; / -- pipelined function is used in FROM clause of SELECT statement SELECT * FROM TABLE(pkg1.f1(5));
パイプライン・テーブル・ファンクションには、通常のファンクションに使用できる引数であれば、すべて使用できます。引数としてREF
CURSOR
を受け入れるテーブル・ファンクションは、変換ファンクションとして使用できます。つまり、REF
CURSOR
を使用して入力行をフェッチし、その変換を実行し、結果をパイプラインで出力できます。
例12-22では、f_trans
ファンクションがemployees
表の1行を2行に変換しています。
例12-22 変換へのパイプライン・テーブル・ファンクションの使用
-- Define the ref cursor types and function 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; EXIT WHEN p%NOTFOUND; -- first row 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); -- second row out_rec.var_char1 := in_rec.email; out_rec.var_char2 := in_rec.phone_number; PIPE ROW(out_rec); END LOOP; CLOSE p; RETURN; END; END refcur_pkg; / -- SELECT query using the f_transc table function SELECT * FROM TABLE( refcur_pkg.f_trans(CURSOR (SELECT * FROM employees WHERE department_id = 60)));
この問合せでは、パイプライン・テーブル・ファンクションf_trans
はCURSOR
副問合せSELECT
*
FROM
employees
...から行をフェッチし、変換を実行して、結果をパイプラインでユーザーに表として戻します。このファンクションでは、入力行ごとに出力行(コレクション要素)が2行ずつ生成されます。
例12-22に示されているように、CURSOR
副問合せがSQLからREF
CURSOR
ファンクションの引数に渡される場合、ファンクションの実行中には参照先のカーソルがすでにオープンされています。
PL/SQLでは、PIPE
ROW
文によってパイプライン・テーブル・ファンクションで行がパイプされ、処理が継続します。この文を使用すると、PL/SQLのテーブル・ファンクションで生成直後に行を戻すことができます。パフォーマンス上の理由から、PL/SQLランタイム・システムでは、行はコンシューマにバッチで与えられます。
例12-22のPIPE
ROW(out_rec)
文では、データをパイプラインでPL/SQLテーブル・ファンクションから戻しています。out_rec
はレコードで、その型は出力コレクションの要素の型と一致します。
PIPE
ROW
文を使用できるのはパイプライン・テーブル・ファンクションの本体内のみで、他の場所で使用すると例外が呼び出されます。行を戻さないパイプライン・テーブル・ファンクションの場合は、PIPE
ROW
文を省略できます。
パイプライン・テーブル・ファンクションでは、値を戻さないRETURN
文を含めることもできます。このRETURN
文は、制御をコンシューマに移し、次回のフェッチでNO_DATA_FOUND
例外が確実に呼び出されるようにします。
テーブル・ファンクションとコール元のルーチンは、行の生成に伴って制御をやり取りするため、テーブル・ファンクションとPRAGMA
AUTONOMOUS_TRANSACTION
の組合せに関する制限があります。テーブル・ファンクションが自律型トランザクションの一部である場合、コール元のサブプログラムでエラーが発生しないように、各PIPE
ROW
文の前にCOMMIT
またはROLLBACK
を実行する必要があります。
データベースには、オブジェクトやコレクション型などの他のSQL型の型記述、データ・インスタンスおよびデータ・インスタンス・セットを動的にカプセル化してアクセスできる3つの特別なSQLデータ型が用意されています。 また、この3つの特別な型を使用すると、匿名コレクション型などの名前を持たない型を作成できます。この3つの型は、SYS
.ANYTYPE
、SYS
.ANYDATA
およびSYS
.ANYDATASET
です。SYS
.ANYDATA
型は、テーブル・ファンクションからの戻り値として役立つ場合があります。
参照: ANYTYPE 、ANYDATA およびANYDATASET 型へのインタフェースと、この3つの型で使用するDBMS_TYPES パッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
シリアル実行では、コルーチン実行に似たアプローチを使用して、結果があるPL/SQLテーブル・ファンクションから別のPL/SQLテーブル・ファンクションへとパイプラインされます。たとえば、次の文では、ファンクションg
からファンクションf
へと結果がパイプラインされます。
SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g()))));
パラレル実行の場合も同様ですが、各ファンクションは異なるプロセス(またはプロセス・セット)で実行されます。
パイプライン・テーブル・ファンクションを同じ問合せまたは別の問合せで複数回コールすると、基礎となる実装が複数回実行されます。 デフォルトでは、行のバッファリングや再利用は行われません。次に例を示します。
SELECT * FROM TABLE(f(...)) t1, TABLE(f(...)) t2 WHERE t1.id = t2.id; SELECT * FROM TABLE(f()); SELECT * FROM TABLE(f());
ファンクションが、渡される値の各組合せに対して常に同じ結果の値を生成する場合、ファンクションDETERMINISTIC
を宣言すると、データベースによって行が自動的にバッファリングされます。ファンクションが実際は非決定的である場合、予測できない結果になります。
PL/SQLのCURSORとREF CURSORは、テーブル・ファンクションに対する問合せ用に定義できます。次に例を示します。
OPEN c FOR SELECT * FROM TABLE(f(...));
テーブル・ファンクションのカーソルと通常のカーソルでは、フェッチの意味は同じです。テーブル・ファンクションに基づくREF
CURSOR
の代入に特別な意味はありません。
ただし、SQLオプティマイザでは、PL/SQL文にまたがる最適化は行われません。次に例を示します。
DECLARE r SYS_REFCURSOR; BEGIN OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab))); SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r)); END; /
前述の例は、次の例と同様には実行されません。
SELECT * FROM TABLE(g(CURSOR(SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab))))));
これは、2つのSQL文の実行に関連するオーバーヘッドを無視して、2つの文の間で結果をパイプラインできると想定した場合も同様です。
PL/SQLファンクションにREF
CURSOR
パラメータで行セットを渡すことができます。たとえば、このファンクションが事前定義された弱い型指定のREF
CURSOR
を持つSYS_REFCURSOR
型の引数を受け入れるように宣言されているとします。
FUNCTION f(p1 IN SYS_REFCURSOR) RETURN ... ;
副問合せの結果をファンクションに直接渡すことができます。
SELECT * FROM TABLE(f(CURSOR(SELECT empid FROM tab)));
この例では、CURSOR
キーワードによって、副問合せの結果がREF
CURSOR
パラメータとして渡されます。
事前定義の弱いREF
CURSOR
型のSYS_REFCURSOR
もサポートされます。 SYS_REFCURSOR
を使用すると、パッケージ内でREF
CURSOR
型を使用前に作成する必要はありません。
強いREF
CURSOR
型を使用する場合は、PL/SQLパッケージを作成し、その中でREF
CURSOR
型を宣言する必要があります。また、強いREF
CURSOR
型をテーブル・ファンクションの引数として使用する場合は、REF
CURSOR
引数の実際の型が列の型と一致する必要があります。一致しない場合は、エラーが生成されます。テーブル・ファンクションの弱いREF
CURSOR
引数をパーティション化するには、PARTITION
BY
ANY
句を使用する必要があります。弱いREF
CURSOR
引数には、レンジ・パーティション化もハッシュ・パーティション化も使用できません。
例12-23に示すように、PL/SQLファンクションは複数のREF
CURSOR
入力変数を受け入れることができます。
カーソル変数の詳細は、「REF CURSOR型およびカーソル変数の宣言」を参照してください。
例12-23 複数のREF CURSOR入力変数の使用
-- Define the ref cursor types CREATE 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; END refcur_pkg; / -- SELECT query using the g_trans table function SELECT * FROM TABLE(refcur_pkg.g_trans( CURSOR(SELECT * FROM employees WHERE department_id = 60), CURSOR(SELECT * FROM departments WHERE department_id = 60)));
戻されたデータを反復するREF
CURSOR
を作成すると、テーブル・ファンクションの戻り値を他のテーブル・ファンクションに渡すことができます。
SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g(...)))));
問合せに対してREF
CURSOR
を明示的にオープンし、それをテーブル・ファンクションにパラメータとして渡すことができます。
DECLARE r SYS_REFCURSOR; rec ...; BEGIN OPEN r FOR SELECT * FROM TABLE(f(...)); -- Must return a single row result set. SELECT * INTO rec FROM TABLE(g(r)); END; /
この場合、テーブル・ファンクションは完了時にカーソルをクローズするため、プログラムではカーソルを明示的にクローズしないようにする必要があります。
テーブル・ファンクションでは、入力REF CURSORを使用して集計結果を計算できます。 例12-24では、一連の入力行を反復することで、加重平均を計算しています。
例12-24 集計ファンクションとしてのパイプライン・テーブル・ファンクションの使用
CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30), weight NUMBER, grade NUMBER); INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4); INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3); INSERT INTO gradereport VALUES('Mark','Maths', 3, 3); INSERT INTO gradereport 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 -- Function accepts ref cursor and loops through all input rows LOOP FETCH input_values INTO weight, grade; EXIT WHEN input_values%NOTFOUND; -- Accumulate the weighted average total_weight := total_weight + weight; total := total + grade*weight; END LOOP; PIPE ROW (total / total_weight); RETURN; -- the function returns a single result END; END pkg_gpa; / -- Query result is a nested table with single row -- COLUMN_VALUE is keyword that returns contents of nested table SELECT w.column_value "weighted result" FROM TABLE( pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;
DML文を実行するには、AUTONOMOUS_TRANSACTION
プラグマを使用してパイプライン・テーブル・ファンクションを宣言します。これによって、ファンクションは、他のプロセスに共有されない新しいトランザクションで実行されます。
CREATE FUNCTION f(p SYS_REFCURSOR) RETURN CollType PIPELINED IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN NULL; END; /
パラレル実行中に、テーブル・ファンクションの各インスタンスが独立したトランザクションを作成します。
パイプライン・テーブル・ファンクションをUPDATE
、INSERT
またはDELETE
文のターゲット表にすることはできません。 たとえば、次の文では例外が呼び出されます。
UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value; INSERT INTO f(...) VALUES ('any', 'thing');
ただし、テーブル・ファンクションのビューを作成し、INSTEAD
OF
トリガーを使用して更新できます。次に例を示します。
CREATE VIEW BookTable AS SELECT x.Name, x.Author FROM TABLE(GetBooks('data.txt')) x;
次のINSTEAD
OF
トリガーは、ユーザーがBookTable
ビューに行を挿入すると起動します。
CREATE TRIGGER BookTable_insert INSTEAD OF INSERT ON BookTable REFERENCING NEW AS n FOR EACH ROW BEGIN ... END / INSERT INTO BookTable VALUES (...);
INSTEAD
OF
トリガーは、テーブル・ファンクションに作成されたビューでのすべてのDML操作について定義できます。