| Oracle Databaseパフォーマンス・チューニング・ガイド 11gリリース1（11.1） E05743-02 |
|
 戻る |
 次へ |
この章では、SQL処理、最適化方式およびSQL文を実行する特定の計画をオプティマイザが選択する方法を説明します。
この章には次の項があります。
SQL文は、全表スキャン、索引スキャン、ネステッド・ループおよびハッシュ結合などにおいて、様々な方法で実行されます。問合せオプティマイザは、問合せで指定した参照オブジェクトおよび条件に関連した多くの要素を考慮してSQL文を最も効率よく実行する方法を判断します。この判断は、SQL文の処理で重要なステップであり、実行時間が大きく変化します。
|
注意: オプティマイザは、Oracle Databaseのあるバージョンとその次のバージョンで同じ決定を行うとはかぎりません。最新バージョンのオプティマイザは、より高度な情報を使用できるので、異なる決定を行います。 |
オプティマイザからは、最適な実行方法を説明する実行計画が出力されます。この計画により、SQL文を実行するためにOracle Databaseで使用するステップの組合せが示されます。実行される各ステップでは、データベースからのデータ行の物理的な取出し、またはユーザーが発行する文に返すデータ行の準備のどちらかが実行されます。
Oracleによって処理されるSQL文について、オプティマイザは表11-1にリストした操作を実行します。
表11-1 オプティマイザ操作
| 操作 | 説明 |
|---|---|
|
式と条件の評価 |
オプティマイザは、まず、定数が含まれている式と条件を可能なかぎり完全に評価します。 |
|
文の変換 |
たとえば、相関副問合せやビューなどに関連する複合文について、オプティマイザは元の文を同等の結合文に変換する場合があります。 |
|
オプティマイザの目標の選択 |
オプティマイザでは、最適化の目標が判別されます。「オプティマイザの目標の選択」を参照してください。 |
|
アクセス・パスの選択 |
文がアクセスするそれぞれの表について、オプティマイザは、表データを取得するために1つ以上の使用可能なアクセス・パスを選択します。「問合せオプティマイザのアクセス・パスについて」を参照してください。 |
|
結合順序の選択 |
2つ以上の表を結合する結合文について、オプティマイザは、最初に結合する表のペアを選択し、その後、その結果に結合する表を順次選択していきます。「問合せオプティマイザによる結合の実行計画の選択方法」を参照してください。 |
Oracleデータベースでは、問合せの最適化が可能です。オプティマイザの目標の設定および問合せオプティマイザの代表的な統計の収集によって、オプティマイザの選択を変えることができます。オプティマイザの目標はスループットまたはレスポンス時間です。「オプティマイザの目標の選択」および「データ・ディクショナリ内の問合せオプティマイザ統計」を参照してください。
特定のアプリケーション・データに関して、オプティマイザよりも多くの情報を持つアプリケーション設計者であれば、より効率よくSQL文を実行する方法を選択できる場合があります。アプリケーション・デザイナはSQL文のヒントを使用して、文の実行方法についてオプティマイザに指示できます。
|
関連項目:
|
デフォルトでは、問合せオプティマイザの目標は最高のスループットです。つまり、CBOは文からアクセスされたすべての行を処理するのに必要な最小リソースを選択します。また最短レスポンス時間を目標とした文の最適化も可能です。つまり、CBOはSQL文からアクセスされた最初の行を処理するのに必要な最小リソースを使用します。
アプリケーションのニーズに基づいて、オプティマイザの目標を選択してください。
Oracle Reportsアプリケーションのように、バッチで実行されるアプリケーションの場合は、最高のスループットを目標に最適化してください。アプリケーションを起動するユーザーの関心は、アプリケーションを完了するために必要な時間のみに向けられているため、通常、バッチ・アプリケーションではスループットの重要度が高くなります。アプリケーションの実行中にユーザーが個々の文の結果を調べることはないため、レスポンス時間はそれほど重要ではありません。
Oracle FormsアプリケーションやSQL*Plusの問合せのような対話形式のアプリケーションの場合は、最短のレスポンス時間を目標に最適化してください。対話形式では、ユーザーは、文がアクセスする最初の行を参照するために待機しています。このため、対話形式のアプリケーションではレスポンス時間が重要となります。
SQL文に対する最適化のアプローチと目標を選択する場合のオプティマイザの動作は、次の要因の影響を受けます。
OPTIMIZER_MODE初期化パラメータで、インスタンスに最適化アプローチを選択するためのデフォルト動作を設定します。指定可能な値およびその説明を表11-2に示します。
表11-2 OPTIMIZER_MODE初期化パラメータ値
初期化ファイル内のパラメータ値の変更、またはALTER SESSION SET OPTIMIZER_MODE文によって、セッション内のすべてのSQL文の問合せオプティマイザの目標を変更できます。たとえば、次のようにします。
初期化パラメータ・ファイル内の次の文は、インスタンスのすべてのセッションの問合せオプティマイザの目標を最短のレスポンス時間に設定します。
OPTIMIZER_MODE = FIRST_ROWS_1
次のSQL文は、現行セッションの問合せオプティマイザの目標を最短のレスポンス時間に変更します。
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;
オプティマイザがコストベースのアプローチをSQL文に使用するときに、文がアクセスする一部の表に統計が存在しない場合、オプティマイザはそれらの表に割り当てられているデータ・ブロック数などの内部情報を使用して表に対する別の統計を見積ります。
各SQL文に対する問合せオプティマイザの目標を指定する場合は、表11-3のヒントのいずれかを使用します。各SQL文にあるこれらのヒントは、いずれも、そのSQL文のOPTIMIZER_MODE初期化パラメータを上書きできます。
表11-3 問合せオプティマイザの目標変更に対するヒント
| ヒント | 説明 |
|---|---|
|
このヒントは、個々のSQL文を最適化して最初のn行を最短レスポンス時間で戻すように、Oracleに指示します。ここでは、nは正の整数です。このヒントでは、SQL文に対して、統計の存在の有無にかかわりなくコストベースのアプローチが使用されます。 |
|
|
このヒントでは、最高のスループットを目標としてSQL文を最適化するために、コストベースのアプローチが明示的に選択されます。 |
問合せオプティマイザで使用される統計は、データ・ディクショナリに格納されます。DBMS_STATSパッケージを使用して、これらのスキーマ・オブジェクト内の物理的な記憶域の特性とデータ配分に関する正確な統計または見積り統計を収集できます。
問合せオプティマイザの有効性を維持するには、データを代表する統計が必要です。偏ったデータという、値の重複数のバリエーションが多いデータが存在する表列については、ヒストグラムを収集する必要があります。
その結果の統計によって、データの一意性と配分についての情報が問合せオプティマイザに提供されます。この情報を使用することにより、問合せオプティマイザは計画コストを精密に計算します。その結果、問合せオプティマイザは最小のコストを基に最良の実行計画を選択できるようになります。
問合せオプティマイザを使用するときに統計が使用不可である場合、OPTMIZER_DYNAMIC_SAMPLING初期化パラメータの設定によって、オプティマイザで動的サンプリングが実行されます。この場合、最高のパフォーマンスを得るために解析時間が遅くなる可能性があるため、オプティマイザに代表的なオプティマイザ統計が必要となります。
この項には、オプティマイザに固有の初期化パラメータが含まれています。次の項は、Oracleのアプリケーションをチューニングするときに特に役に立ちます。
|
関連項目: 初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
OPTIMIZER_FEATURES_ENABLE初期化パラメータを設定して、オプティマイザの機能を有効化できます。
OPTIMIZER_FEATURES_ENABLEパラメータ
OPTIMIZER_FEATURES_ENABLEパラメータは、問合せオプティマイザのアンブレラ・パラメータの役割を果します。リリースにより異なりますが、このパラメータを使用して一連のオプティマイザ関連機能を有効にできます。このパラメータは、8.0.4、8.1.7および9.2.0などのリリース番号に対応する有効な文字列値のリストのうちの1つを受け入れます。たとえば、次の設定は、Oracle10gリリース1（10.1）の問合せ計画の生成時のオプティマイザ機能を使用可能にします。
OPTIMIZER_FEATURES_ENABLE=10.0.0;
OPTIMIZER_FEATURES_ENABLEパラメータは、Oracleサーバーをアップグレード可能にすること、さらにアップグレード後も問合せオプティマイザの以前の動作を保持できることを主な目標として導入されました。たとえば、リリース8.1.5からリリース8.1.6にOracleサーバーをアップグレードすると、OPTIMIZER_FEATURES_ENABLEパラメータのデフォルトの値は8.1.5から8.1.6に変わります。このアップグレードのために、問合せオプティマイザでは、8.1.5ではなく8.1.6に基づいた最適化機能が有効にされます。
プラン・スタビリティまたは下位互換性の理由から、問合せ計画が新規リリースのオプティマイザ機能によって変更されないようにする場合もあります。そのような場合は、OPTIMIZER_FEATURES_ENABLEパラメータを以前のバージョンに設定します。たとえば、問合せオプティマイザの動作をリリース8.1.5に保持するには、次のようにパラメータを設定します。
OPTIMIZER_FEATURES_ENABLE=8.1.5;
この文により、8.1.5より後のリリースで追加されたすべての新規オプティマイザ機能が無効になります。リリースをアップグレードし、使用可能な新機能を有効にする場合は、OPTIMIZER_FEATURES_ENABLE初期化パラメータを明示的に設定する必要はありません。
|
注意: 以前のリリースにOPTIMIZER_FEATURES_ENABLEパラメータを明示的に設定することはお薦めしません。実行計画の変更から生じる可能性があるSQLパフォーマンスの低下を回避するには、かわりに、SQL計画の管理を使用することを検討してください。詳細は、第15章「SQL計画の管理の使用方法」を参照してください。 |
|
関連項目: OPTIMIZER_FEATURES_ENABLEパラメータを各リリースの値に設定すると有効になるオプティマイザ機能の詳細は、『Oracle Databaseリファレンス』を参照してください。 |
この項には、問合せオプティマイザの動作の管理に使用できる初期化パラメータの一部がリストされています。SQL実行のパフォーマンスを向上するために、これらのパラメータを使用して様々なオプティマイザ機能を有効にすることができます。
このパラメータは、SQL文のリテラル値をバインド変数に変換します。値を変換するとカーソル共有が改善され、SQL文の実行計画は影響を受けます。オプティマイザは、実際のリテラル値でなくバインド変数の有無に基づいて実行計画を生成します。
このパラメータは、全表スキャンまたは高速全索引スキャン時に単一I/Oで読み取られるブロックの個数を指定します。オプティマイザは、DB_FILE_MULTIBLOCK_READ_COUNTの値を使用して、全表スキャンと高速全索引スキャンのコストを計算します。値が大きいほど全表スキャンのコストは低くなり、オプティマイザは索引スキャンより全表スキャンを選択します。このパラメータを明示的に設定しない場合（または0に設定する場合）、デフォルト値は、効率的に実行可能な、プラットフォームに依存する最大I/Oサイズに相当します。
このパラメータは、ネステッド・ループとともに索引プローブのコスト計算の管理に使用します。OPTIMIZER_INDEX_CACHINGの0から100の範囲は、ネステッド・ループおよびINリスト・イテレータの索引キャッシュに関するオプティマイザの仮定を変更するバッファ・キャッシュ内の索引ブロックのキャッシュ率を管理します。この値が100となっている場合、索引ブロックの100%がバッファ・キャッシュに見つかる可能性が推測されます。オプティマイザはそれに応じて索引プローブあるいはネステッド・ループのコストを調整します。実行計画は索引のキャッシュに応じて変更される可能性があります。このパラメータを使用するときは注意してください。
このパラメータを使用して、索引プローブのコストを調整できます。値の範囲は1から10000です。デフォルト値は100ですが、これは索引が標準のコスト計算モデルに基づいてアクセス・パスとして評価されることを意味します。値10は、索引アクセス・パスのコストが標準コストの1/10であることを意味します。
この初期化パラメータは、インスタンスの起動時のオプティマイザのモードを設定します。可能な値は、ALL_ROWS、FIRST_ROWS_nおよびFIRST_ROWSです。これらのパラメータ値の詳細は、「OPTIMIZER_MODE初期化パラメータ」を参照してください。
このパラメータは、ソートおよびハッシュ結合に割り当てられるメモリーの量を自動的に制御します。ソートまたはハッシュ結合に大量のメモリーが割り当てられると、これらの操作のオプティマイザ・コストが減少します。「PGAメモリー管理」を参照してください。
このパラメータをtrueに設定すると、問合せオプティマイザはスター・クエリーのためのスター型変換のコストを計算できます。スター型変換により、様々なファクト表の列でビットマップ索引が結合されます。
|
関連項目: 各パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
問合せオプティマイザは、SQL文がアクセスするスキーマ・オブジェクト（表または索引）について、使用可能なアクセス・パスを検討し、統計に基づいた情報要素を考慮することによって最も効果的な実行計画を判断します。また、問合せオプティマイザは文のコメントに配置された、最適化の指示であるヒントも考慮します。
問合せオプティマイザは次のステップを実行します。
使用可能なアクセス・パスおよびヒントに基づき、SQL文の可能な計画のセットを生成します。
文がアクセスする表、索引およびパーティションのデータ配分および記憶特性に関するデータ・ディクショナリ内の統計に基づき、計画のそれぞれのコストを見積ります。
コストとして見積られる値は、特定の計画での文の実行に必要と予測されるリソース使用量に比例しています。オプティマイザは、I/O、CPU、メモリーなどのコンピュータ・リソースの見積りに基づいて、アクセス・パスや結合順序のコストを計算します。
コストの大きいシリアル計画の実行には、コストの小さい計画の実行よりも多くの時間が必要です。ただし、パラレル計画を使用する場合は、リソース使用量は経過時間に直接関係しません。
計画のコストを比較して、コストが最も小さいものを選択します。
問合せオプティマイザ操作には、次のものがあります。
問合せオプティマイザの構成要素を図11-1に示します。
問合せトランスフォーマへの入力は、一連の問合せブロックによって表される解析済問合せです。問合せブロックは、互いにネストされているかまたは相互関係を持っています。問合せの形式によって、問合せブロックの相互関係が判断されます。問合せトランスフォーマの主な目的は、問合せの形式を変える必要があるかどうかを判断して、より適切な問合せ計画を生成できるようにすることです。問合せトランスフォーマでは、次のように様々な問合せ変換手法を採用しています。
これらの変換は任意に組み合せて指定の問合せに適用できます。
問合せで参照されるビューは、パーサーによって個別の問合せブロックに拡張されます。問合せブロックは、基本的にはビュー定義を表しますが、このため必然的にビューの結果も表すことになります。オプティマイザには、ビューの問合せブロックの1つを分離して分析し、ビュー・サブプランを生成するというオプションがあります。オプティマイザは、問合せ計画全体の生成にビュー・サブプランを使用することで、残りの問合せを処理します。この手法は、そのビューが問合せの残りの部分とは別に最適化されるため、通常は最適な問合せ計画とはなりません。
問合せトランスフォーマは、ビューが含まれている問合せブロックにビューの問合せブロックをマージして、潜在的に最適とは言えない計画を除去します。ほとんどのタイプのビューがマージされます。ビューをマージするときには、ビューを表す問合せブロックが包含的な問合せブロックにマージされます。ビューの問合せブロックは除去されているので、サブプランを生成する必要はありません。
ユーザーが発行したすべての問合せについて、ビューのマージを使用するオプティマイザを有効にするには、MERGE ANY VIEW権限をユーザーに付与します。これらのビューに対する問合せについて、ビューのマージを使用するオプティマイザを有効にするには、MERGE VIEW権限をユーザーに付与します。これらの権限は、セキュリティで障害をチェックするためにビューがマージされない場合などの特定の状況でのみ必要です。
|
関連項目:
|
マージされていないビューについては、問合せトランスフォーマにより、関連する述語を、ビューを包含する問合せブロックからビューの問合せブロックに組み込むことができます。この手法は、プッシュされた述語を索引へのアクセスやフィルタに使用できるので、マージされていないビューのサブプランが改善されます。
通常、副問合せを含む問合せのパフォーマンスは、副問合せのネストを解除して結合に変換することで改善できます。大半の副問合せは、問合せトランスフォーマでネスト解除されます。ネスト解除されない副問合せの場合は、個別のサブプランが生成されます。問合せ計画全体の実行速度を上げるため、サブプランは効果的な順序で並べられます。
マテリアライズド・ビューは、結果がマテリアライズされて表に格納された問合せと類似しています。マテリアライズド・ビューに関連付けられた問合せと互換性のあるユーザー問合せが検出されると、ユーザー問合せはマテリアライズド・ビューにリライトできます。この手法は、ほとんどの問合せ結果があらかじめ計算されているため、ユーザー問合せの実行状態を改善します。問合せトランスフォーマは、ユーザー問合せと互換性のあるマテリアライズド・ビューを検索し、1つ以上のマテリアライズド・ビューを選択してユーザー問合せをリライトします。問合せをリライトするためのマテリアライズド・ビューの使用はコストベースです。したがって、マテリアライズド・ビューを使用せずに生成された計画のコストが、マテリアライズド・ビューを使用して生成された計画のコストより低い場合、問合せはリライトされません。
問合せオプティマイザは、カーソルの最初の起動時にユーザー定義バインド変数の値を照合します。この機能により、WHERE句の条件の選択性と、バインド変数のかわりにリテラルが使用されたかどうかが判断されます。
特定のバインド値に対して最適なカーソルを確実に選択するために、Oracle Databaseではバインド対応カーソル・マッチングを使用します。バインド値に応じて、問合せによって実行されるデータ・アクセスは長期的に監視されます。バインド照合が発生し、ヒストグラムを使用してバインド変数を含む述語の選択性が計算される場合、カーソルはバインド依存カーソルとマークされます。カーソルがバインド値に応じて大きく異なるデータ・アクセス・パターンを作成する場合は常にバインド対応とマークされ、その文に対するカーソルを選択するためにバインド対応カーソル・マッチングに切り替わります。バインド対応カーソル・マッチングを使用可能にすると、バインド値とその選択性のオプティマイザによる見積りに基づいて、計画は選択されます。バインド対応カーソル・マッチングの場合、ユーザー定義のバインド変数を含むSQL文は、そのバインド値に応じて複数の実行計画を保持する可能性があります。
バインド変数がSQL文に使用されている場合、カーソルを共有するために異なる起動が同じ実行計画を使用するとみなします。カーソルの異なる起動で様々な実行計画を効果的に利用する場合、バインド対応カーソル・マッチングが必要です。バインド照合は、すべてのクライアントではなく特定のクライアント・セットに対して機能します。
次の例を見てください。
SELECT avg(e.salary), d.department_name
FROM employees e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;
この例では、列job_idに偏りがあります。副社長（job_id = 'AD_VP'）より営業担当（job_id = 'SA_REP'）の方がずっと多く存在するためです。したがって、この問合せの最良の計画は、バインド変数の値に応じて変化します。この場合、job_idがAD_VPの場合は索引を使用し、job_idがSA_REPの場合は全表スキャンを使用するのが効率的です。オプティマイザは、最初の値（'AD_VP'）を照合して索引を選択します。また、カーソルはバインド依存カーソルとしてマークされます。問合せの次回実行時に、バインド値がMK_REP（営業担当）でこのバインド値の選択性が低い場合、オプティマイザはカーソルをバインド対応としてマークし、文をハード解析して全表スキャンを実行する新規計画を生成します。
選択性の範囲、カーソル情報（カーソルがバインド対応またはバインド依存のどちらかなど）および実行統計は、拡張カーソル共有に対してV$ビューを使用すると取得できます。V$SQL_CS_STATISTICSビューは、各カーソルの実行統計を含んでおり、異なるバインド設定で生成されるカーソルの実行を比較してパフォーマンスをチューニングするのに使用できます。
|
関連項目: クエリー・リライトの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
エスティメータは、異なるタイプのメジャーを3通り生成します。
これらのメジャーは相互に関連性があり、あるメジャーは別のメジャーから派生します。エスティメータの最終目標は、指定された計画のコスト全体を算出することです。統計が使用可能な場合、エスティメータは統計を使用してメジャーを計算します。統計によって、メジャーの正確さの度合いは改良されます。
最初のメジャーは行セットの一部の行を表す選択性です。行セットとは、実表、ビュー、結合結果またはGROUP BY演算子の結果です。選択性は、last_name = 'Smith'などの問合せ述語、またはlast_name = 'Smith' AND job_type = 'Clerk'などの述語の組合せに拘束されています。述語はフィルタとして機能します。このフィルタは、行セットから特定数の行を選別します。したがって、述語の選択性が述語テストに合格する行セットの行数を示しています。選択性の値範囲は、0.0から1.0です。選択性が0.0の場合、行セットから行は選択されず、選択性が1.0の場合はすべての行が選択されます。
使用可能な統計が存在しない場合、オプティマイザは、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータの値に応じて動的サンプリングまたは内部デフォルト値を使用します。使用される内部デフォルトは、述語のタイプによって異なります。たとえば、等価述語（last_name = 'Smith'）の内部デフォルトは範囲述語（last_name > 'Smith'）の内部デフォルトより低くなっています。エスティメータがこの仮定を行うのは、等価述語が範囲述語より少ない行数の行を戻すことが予測されるためです。「動的サンプリングを使用した統計の見積り」を参照してください。
統計が使用可能な場合、エスティメータは統計を使用して選択性を推測します。たとえば、等価述語（last_name = 'Smith'）の選択性は、last_nameの個別値である数値nの逆数に設定されます。これは、問合せがnの内の1つの個別値を含む行をすべて選択するためです。last_name列でヒストグラムが使用可能な場合、エスティメータは個別値のかわりにヒストグラムを使用します。ヒストグラムには列内の異なる値の分散が示されているので、より適切な選択性の見積りが行われます。偏ったデータ（値の重複数のバリエーションが多いデータ）が含まれている列のヒストグラムが存在すると、問合せオプティマイザが適切な選択性の見積りを生成するのに役立ちます。
カーディナリティは、行セット内の行数を表します。ここでの行セットとは、実表、ビュー、結合結果またはGROUP BY演算子の結果です。
コストは、作業単位または使用されるリソースを表します。問合せオプティマイザはディスクI/O、CPU使用量、メモリー使用量を作業単位として使用します。しかし、問合せオプティマイザによって使用されるコストは、操作の実行で使用したCPUとメモリーの量の見積り数になります。すなわち、操作には表をスキャンすること、索引を使用して表から行にアクセスすること、2つの表を結合すること、または行セットをソートすることなどがあります。問合せ計画のコストは、問合せが実行されてその結果が生成されるときに発生すると予測される作業単位の数です。
アクセス・パスは、実表からデータを得るときに実行される作業単位数です。アクセス・パスには、表スキャン、高速全索引スキャンまたは索引スキャンがなどがあります。表スキャンまたは高速全索引スキャンの実行中には、単独のI/O操作で複数のブロックがディスクから読み取られます。したがって、表スキャンまたは高速全索引スキャンのコストは、スキャンされるブロック数およびマルチブロックREADカウントに左右されます。索引スキャンのコストは、Bツリーにおけるレベル、つまりスキャンされる索引リーフ・ブロック数、索引キーのROWIDを使用してフェッチされる行数に左右されます。ROWIDを使用して行をフェッチするコストは、索引クラスタ化係数に依存します。「ブロックのI/O（行ではなく）の想定」を参照してください。
結合コストは、結合されている2つの行セットの個別のアクセス・コストの組合せと、結合操作のコストを表します。
プラン・ジェネレータの主な機能は、指定の問合せに対して使用できる可能性のある別の計画を割り出し、コストの最も低いものを取り出すことです。異なる方法でデータをアクセスおよび処理し、かつ結果が同じになる、様々なアクセス・パス、結合方法および結合順序の組合せが存在するので、多数の異なる計画が使用できます。
結合順序は、異なる結合項目（表など）がアクセスされて結合される順序です。たとえば、table1、table2およびtable3の結合順序では、表table1が最初にアクセスされます。次に、table2がアクセスされ、そのデータがtable1のデータに結合されてtable1とtable2の結合が生成されます。最後にtable3がアクセスされ、そのデータがtable1とtable2の結合結果に結合されます。
問合せのための計画は、まず最初にネストされた副問合せおよびマージされていないビューのそれぞれにサブプランを生成することによって構築されます。ネストされた副問合せ、またはマージされていないビューは、それぞれ、個別の問合せブロックによって表されます。問合せブロックは、それぞれ、下位から上位へ順番に最適化されます。つまり、最も内側の問合せブロックが最初に最適化され、サブプランが生成されます。そして、問合せ全体を表す一番外側の問合せブロックは、最後に最適化されます。
プラン・ジェネレータは、別のアクセス・パス、結合方法および結合順序を試行することによって、問合せブロックに対する様々な計画を探索します。問合せブロックに使用できる可能性のある計画の数は、FROM句にある結合項目の数に比例します。この数は、結合項目の数によって指数関数的に上昇します。
プラン・ジェネレータは内部カットオフを使用して計画数を削減し、最もコストの低い計画を検索しようとします。カットオフの基準は、現行の最適な計画のコストです。現行の最適コストが大きい場合、プラン・ジェネレータはコストがより低く、より適切な計画（つまり、さらに別の計画）を探索します。現行の最適コストが低い場合は、これ以上のコストの改善を追及しても大きな効果は得られないため、プラン・ジェネレータは検索を速やかに終了します。
最適な状態に最も近いコストで計画を生成する初期結合順序で、プラン・ジェネレータが始動する場合は、カットオフが有効に機能します。適切な初期結合順序の検索は困難です。
SQL文を実行するために、Oracleは、多数のステップを実行する必要があります。実行される各ステップでは、データベースからのデータ行の物理的な取出し、またはユーザーが発行する文に返すデータ行の準備のどちらかが実行されます。文を実行するためにOracleが使用するステップの組合せのことを実行計画と呼びます。実行計画には、文がアクセスする各表へのアクセス・パスと、適切な結合方法に基づく表の順序（結合順序）が含まれています。
EXPLAIN PLAN文を使用することにより、オプティマイザがSQL文に対して選択した実行計画を確認できます。文が発行されると、オプティマイザが実行計画を選択した後で、計画を説明するデータがデータベース表に挿入されます。単純に、EXPLAIN PLAN文を発行し、出力表を問い合せます。
EXPLAIN PLAN文の使用方法の基本は次のとおりです。
SQLスクリプトUTLXPLAN.SQLを使用し、使用しているスキーマ内にPLAN_TABLEというサンプル出力表を作成してください。「PLAN_TABLE出力表」を参照してください。
SQL文の前にEXPLAIN PLAN FOR句を挿入します。「EXPLAIN PLANの実行」を参照してください。
EXPLAIN PLAN文を発行した後、Oracle Databaseから提供されるスクリプトまたはパッケージのいずれかを使用して最新の計画表出力を表示します。「PLAN_TABLE出力の表示」を参照してください。
EXPLAIN PLANの出力実行順序は、最も右端にインデントされている行から始まります。次のステップは、その行の親です。2つの行が等しくインデントされている場合、通常、最上位の行が最初に実行されます。
例11-1ではEXPLAIN PLANを使用して、IDが103より小さい従業員の、employee_id、job_title、salaryおよびdepartment_nameを選択するSQL文について説明しています。
例11-1 EXPLAIN PLANの使用方法
EXPLAIN PLAN FOR
SELECT e.employee_id, j.job_title, e.salary, d.department_name
FROM employees e, jobs j, departments d
WHERE e.employee_id < 103
AND e.job_id = j.job_id
AND e.department_id = d.department_id;
例11-2の結果の出力表では、例にあるSQL文を実行するためにオプティマイザで選択された実行計画が示されています。
例11-2 EXPLAIN PLAN出力
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|
| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPLOYEE_ID"<103)
5 - access("E"."JOB_ID"="J"."JOB_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3 | 189 | 8 (13)| 00:00:01 |
| 3 | MERGE JOIN | | 3 | 141 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 3 | 60 | 3 (34)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 3 | | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."JOB_ID"="J"."JOB_ID")
filter("E"."JOB_ID"="J"."JOB_ID")
8 - access("E"."EMPLOYEE_ID"<103)
9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
出力表内の各行は、実行計画内の1つのステップに対応しています。アスタリスクの付いたステップIDは、Predicate Informationセクションにリストされています。
実行計画の各ステップで、行のセットが戻されます。この行のセットは、次のステップで使用されます。最後のステップでは、SQL文を発行しているユーザーまたはアプリケーションに対し、行のセットが戻されます。各ステップで戻される行のセットを、行セットと呼びます。
ステップIDの番号は、EXPLAIN PLAN文で返された実行計画の順序IDに対応しています。実行計画の各ステップでは、データベースから行を取り出すか、1つ以上の行ソースから行を入力として受け入れます。
例11-2の次のステップでは、データベース内のオブジェクトからデータが物理的に取り出されます。
ステップ3ではemployees表にあるすべての行を読み取ります。
ステップ5では、JOB_ID_PK索引の各job_idを参照して、jobs表内の対応する行のROWIDを検索します。
ステップ4では、jobs表からステップ5で戻されたROWIDを持つ行を取得します。
ステップ7では、DEPT_ID_PK索引の各department_idを参照して、departments表内の対応する行のROWIDを検索します。
ステップ6では、departments表からステップ7で戻されたROWIDを持つ行を取得します。
例11-2の次のステップでは、直前の行ソースから戻された行を処理します。
ステップ2では、ステップ3およびステップ4から戻される行ソースを受け入れ、ステップ3からの各行ソースをステップ4の対応する行に結合し、その結果の行をステップ2に戻す、ネステッド・ループ操作を、jobs表およびemployees表のjob_idで実行します。
ステップ1では、ステップ2およびステップ6から戻される行ソースを受け入れ、ステップ2からの各行をステップ6の対応する行に結合し、その結果の行をステップ1に戻すネステッド・ループ操作を実行します。
アクセス・パスは、データベースからデータを取り出す経路です。一般に、表の行の小さいサブセットを取得する文には索引アクセス・パスを指定する必要がありますが、表の大きい部分にアクセスするときは全体スキャンのほうが効率がよくなります。オンライン・トランザクション処理（OLTP）アプリケーションは、選択性が高く実行の短いSQL文から構成されており、多くの場合は索引アクセス・パスを使用するという特徴があります。それに対して、意思決定支援システムはパーティション表を使用し、関連するパーティションの全体スキャンを実行する傾向があります。
この項では、表内の任意の行の検索および取出しに使用できるデータ・アクセス・パスについて説明します。
このタイプのスキャンでは、表にあるすべての行の読取り、選択基準を満たしていない行のフィルタが実行されます。全表スキャンで、最高水位標以下の、表中のすべてのブロックがスキャンされます。最高水位標は、使用済領域の量、またはデータを受け取るようにフォーマットされている領域を示します。各行が文のWHERE句を満たすかどうかを判断するために、各行が検査されます。
全表スキャンを行うと、ブロックが順に読み取られます。ブロックは隣接しているため、単一ブロックより大きいI/Oコールを使用してプロセスを高速化できます。リード・コールのサイズの範囲は、1ブロックから初期化パラメータDB_FILE_MULTIBLOCK_READ_COUNTで示されるブロック数までです。マルチブロックREADを使用すると、全表スキャンを効率よく実行できます。各ブロックは1回のみ読み取られます。
例11-2「EXPLAIN PLAN出力」には、employees表の全表スキャン例が含まれています。
表の中の大部分のブロックにアクセスする場合は、索引レンジ・スキャンより全表スキャンのほうがコストが低くなります。これは、全表スキャンのほうが大きいI/Oコールを使用しており、少数の大きいI/Oコールのほうが、多数の小さいI/Oコールよりもコストが低いためです。
オプティマイザは、次のいずれかの場合に全表スキャンを使用します。
問合せで既存の索引を使用できない場合、全表スキャンを使用します。たとえば、索引付きの列で使用される関数が問合せ内にある場合、オプティマイザは索引を使用できず、かわりに全表スキャンを使用します。
大/小文字を区別しない検索に索引を使用する必要がある場合は、大/小文字混合データを検索列に許可しないか、検索列にUPPER(last_name)のようなファンクション索引を作成します。「パフォーマンスを考慮したファンクション索引の使用方法」を参照してください。
問合せが表のブロックの大部分にアクセスするとオプティマイザが判断した場合、索引が使用できる場合でも全表スキャンが使用される可能性があります。
1回のI/Oコールで読み取れる、最高水位標以下のDB_FILE_MULTIBLOCK_READ_COUNTより少ないブロックが表に格納されている場合には、表のどの部分にアクセスされるかや索引の有無に関係なく、全表スキャンを行う方が索引レンジ・スキャンよりもコストが低くなる可能性があります。
表の並列度が高いと、レンジ・スキャンよりも全表スキャンの方向にオプティマイザを偏らせます。表の並列度を判断するには、ALL_TABLES内のDEGREE列を調べます。
オプティマイザに全表スキャンの使用を指示する場合は、ヒントFULL(table alias)を使用します。FULLヒントの詳細は、「アクセス・パスに関するヒント」を参照してください。
取得したブロックがバッファ・キャッシュのどこに置かれるかを示すには、CACHEおよびNOCACHEヒントを使用できます。CACHEヒントでは、全表スキャンを実行する際、オプティマイザに指示して、取得されたブロックがバッファ・キャッシュ内で最後に使用されたLRUリストの最後に配置されるようにします。
小規模表は、表11-4の基準に従って自動的にキャッシュされます。
表11-4 表のキャッシュ基準
| 表サイズ | サイズ基準 | キャッシュ |
|---|---|---|
|
小規模 |
ブロックの数が20より少ない、またはキャッシュされているブロックの合計の2%のうち、大きいもの |
|
|
中規模 |
小規模表よりも大きいが、キャッシュされているブロックの合計が10%より少ない |
Oracleでは、表スキャンおよびワークロードの履歴に基づいて表をキャッシュするかどうか判別します。今後の表スキャンでキャッシュされるブロックが見つかる可能性がある場合にのみ、表がキャッシュされます。 |
|
大規模 |
キャッシュされているブロックの合計が10%より大きい |
キャッシュされません。 |
小規模表の自動キャッシュは、CACHE属性で作成または変更された表に対しては使用禁止です。
行のROWIDには、行が含まれているデータファイルおよびデータ・ブロックと該当するブロック内の位置を指定します。行のROWIDの特定による、行の位置特定は、単一行を取得する最も高速な方法です。これは、取得する行のデータベース内での正確な位置が指定されるためです。
ROWIDを使用して表にアクセスする場合、まず、Oracleは選択された行のROWIDを、文のWHERE句、または1つ以上の表の索引の索引スキャンを使用して取得します。次に、OracleはROWIDに従って、それぞれの選択された行を表から探します。
例11-2「EXPLAIN PLAN出力」では、索引スキャンがjobs表およびdepartments表に対して実行されます。取り出されたROWIDは、行データを戻す場合に使用します。
通常、これは索引からROWIDを取得した後の第2のステップです。索引内に存在しない文の中の列には、表アクセスが必要になる場合があります。
ROWIDによるアクセスでは、すべての索引スキャンに従う必要はありません。文に必要な列がすべて索引に含まれていると、ROWIDによる表アクセスは行われない場合があります。
|
注意: ROWIDは、データが格納されている場所を表すOracleの内部表現です。ROWIDはバージョン間で変更される場合があります。位置に基づいたデータのアクセスは、お薦めしません。行の移行や連鎖によって、行が移動するためです。また、エクスポートやインポートの後も同様です。外部キーは主キーに基づいている必要があります。ROWIDの詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。 |
この方法では、文で指定された索引付きの列の値を使用して索引が検索され、行が取得されます。索引スキャンでは、索引の1つ以上の列値に基づいて索引からデータが取得されます。索引スキャンを実行するために、Oracleは文によってアクセスされた列に対応する索引を検索します。文が索引付けされた列にしかアクセスしない場合、Oracleは索引付けされた列値を表からではなく、索引から直接読み取ります。
索引には、索引の値の他に、その値を持っている行のROWIDも含まれています。したがって、索引付けされた列の他に別の列にも文がアクセスする場合、Oracleは、ROWIDまたはクラスタ・スキャンによる表アクセスのどちらかを使用して、表内の行を検索できます。
索引スキャンには次のタイプがあります。
Oracleは、ブロック単位でI/Oを実行します。したがって、全表スキャンを使用するかどうかのオプティマイザの決定は、行でなくアクセスされるブロックのパーセンテージに影響されます。これを索引クラスタ化係数といいます。ブロックに単一行が含まれている場合、アクセスされる行とアクセスされるブロックは同じです。
ただし、大半の表には各ブロック内に複数の行があります。したがって、目的の行が、少ないブロック内にまとまってクラスタ化されていたり、大量のブロックにわたって拡散されていることがあります。
クラスタ化係数は索引のプロパティですが、実際には、表のデータ・ブロック内の類似した索引付き列の値の拡散度合いに関連します。低いクラスタ化係数は、個々の行が表の少数のブロック内に集中されることを示します。逆に、高いクラスタ化係数は、個々の行が表の複数のブロックによりランダムに分散されることを示します。したがって、高いクラスタ化係数の場合はレンジ・スキャンを使用してROWIDで行をフェッチするので、よりコストがかかります。データを戻すために表の中のさらに多くのブロックにアクセスする必要があるためです。例11-3では、クラスタ化係数がコストにどのような影響を与えるかが示されています。
9つの行を持つ表があります。
表のcol1に一意でない索引があります。
c1列は現在、値A、BおよびCを格納しています。
表には、Oracleブロックが3つしかありません。
ケース1: 次の図のように配置されている場合、行に対して索引クラスタ化係数は低くなります。
Block 1 Block 2 Block 3
------- ------- --------
A A A B B B C C C
これは、c1に対して同じ索引付きの列の値を持つ行が、表の中の同じ物理ブロック内にあるためです。レンジ・スキャンを使用して、値Aを持つすべての行を戻す際のコストが低いのは、表の中のブロックを1つのみ読み取れば済むためです。
ケース2: 同じ行が、索引値が表ブロック間に分散する（並べるのではなく）ように再配置されると、索引クラスタ化係数が高くなります。
Block 1 Block 2 Block 3
------- ------- --------
A B C A B C A B C
これは、表の中の3つのブロックを、col1内に値Aを持つすべての行を取得するために、すべて読み取る必要があるためです。
このスキャンは1つのROWIDしか戻しません。単一行にしかアクセスしないことが保証されているUNIQUE制約またはPRIMARY KEY制約が文に存在する場合、Oracleは一意スキャンを実行します。
例11-2「EXPLAIN PLAN出力」では、jobs表およびdepartments表で、それぞれjob_id_pk索引とdept_id_pk索引を使用して索引スキャンが実行されます。
このアクセス・パスは、一意（Bツリー）索引または主キー制約の結果作成された索引の、すべての列が等価条件で指定される場合に使用します。
|
関連項目: 索引構造の詳細とBツリーの検索方法の詳細は、『Oracle Database概要』を参照してください。 |
一般に、一意スキャンを行うためのヒントを使用する必要はありません。ただし、表がデータベース・リンクにまたがっていて、ローカル表からアクセスされる場合や、表が小さく、オプティマイザが全表スキャンを選択する場合があります。
ヒントINDEX(alias index_name)は使用する索引を指定しますが、アクセス・パス（レンジ・スキャンや一意スキャン）は指定しません。INDEXヒントの詳細は、「アクセス・パスに関するヒント」を参照してください。
索引レンジ・スキャンは、選択性の高いデータにアクセスする共通の操作です。このスキャンは、境界（両側で境界付き）スキャンまたは非有界（片側または両側で）スキャンとすることができます。データは、索引列の昇順に戻されます。同じ値を持つ複数の行は、ROWIDで昇順にソートされます。
データを一定順序でソートする必要がある場合は、ORDER BY句を使用し、索引には依存しません。索引を使用してORDER BY句を満たすことができる場合、オプティマイザはこのオプションを使用し、ソートを回避します。
例11-4では、順序がレガシー・システムからインポートされており、レガシー・システムで使用された参照順に問合せを行います。この参照がorder_dateであると仮定します。
例11-4 索引レンジ・スキャン
SELECT order_status, order_id
FROM orders
WHERE order_date = :b1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (34)|
| 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 20 | 3 (34)|
|* 2 | INDEX RANGE SCAN | ORD_ORDER_DATE_IX | 1 | | 2 (50)|
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDERS"."ORDER_DATE"=:Z)
これは選択性の高い問合せである必要があり、問合せでは列の索引を使用して目的の行を取得します。戻されたデータは、order_dateのROWIDにより昇順でソートされます。索引列order_dateは、ここで選択された行と同じなので、データはROWIDでソートされます。
オプティマイザは、次のように条件で指定された索引の1つ以上の先頭列を検出したとき、レンジ・スキャンを使用します。
col1 = :b1
col1 < :b1
col1 > :b1
索引内の先頭列に対する前述の条件のAND組合せ
col1 like 'ASD%'によるワイルド・カード検索は、先頭で行わないでください。これを先頭に置くと、条件col1 like '%ASD'を使用した検索がレンジ・スキャンになりません。
レンジ・スキャンでは、一意索引または非一意索引を使用できます。レンジ・スキャンでは、索引列がORDER BY/GROUP BY句を構成しているときにソートを回避します。
オプティマイザが他の索引を選択したり、全表スキャンを使用する場合は、ヒントが必要になる場合があります。ヒントINDEX(table_alias index_name)では、特定の索引を使用するようにオプティマイザに指示します。INDEXヒントの詳細は、「アクセス・パスに関するヒント」を参照してください。
索引レンジ・スキャン降順は、データが降順で戻されること以外、索引レンジ・スキャンと同じです。デフォルトでは、索引は昇順に格納されます。通常、このスキャンが使用されるのは、最初に最新のデータを戻すためにデータを降順に並べる場合や、指定された値より小さい値を探す場合です。
ヒントINDEX_DESC(table_alias index_name)は、このアクセス・パスに使用します。INDEX_DESCヒントの詳細は、「アクセス・パスに関するヒント」を参照してください。
索引スキップ・スキャンにより、接頭辞の付いていない列による索引スキャンが改善されます。多くの場合、索引ブロックをスキャンするほうが、表データ・ブロックをスキャンするより高速です。
スキップ・スキャンにより、コンポジット索引をさらに小さい副索引に論理的に分割できます。スキップ・スキャンでは、コンポジット索引の初期列が問合せで指定されていません。つまり、その列がスキップされます。
論理副索引の個数は、初期列内の個別値の数で決まります。スキップ・スキャンは、コンポジット索引の先頭列に個別値がほとんどなく、索引の非先頭キーに値が多数ある場合に便利です。
例11-5 索引スキップ・スキャン
たとえば、コンポジット索引（sex、employee_id）を持つ表employees（sex、employee_id、address）を想定します。このコンポジット索引を分割すると、結果は2つの論理副索引MおよびFになります。
この例で、次の索引データがあるとします。
('F',98)
('F',100)
('F',102)
('F',104)
('M',101)
('M',103)
('M',105)
索引は、論理的に次の2つの副索引に分割されます。
値Fを持つキーがある第1の副索引。
値Mを持つキーがある第2の副索引。
列sexが、次の問合せでスキップされます。
SELECT * FROM employees WHERE employee_id = 101;
索引の完全なスキャンは行われませんが、まず値Fを持つ副索引が検索され、次に値Mを持つ副索引が検索されます。
全索引スキャンを行うとソート操作が必要なくなります。これはデータが索引キーで並べられるためです。全体スキャンではブロックが単独で読み取られます。全体スキャンは次のいずれかの状況で使用されます。
次の要件を満たすORDER BY句が問合せに存在する場合。
ORDER BY句の列すべてが索引に含まれている。
ORDER BY句の列の順序が先行する索引列の順序と一致している。
ORDER BY句には、索引のすべての列、または索引内の列のサブセットを含めることができます。
問合せにソート/マージ結合が必要な場合。問合せが次の要件を満たす状態で、全表スキャンの後にソートを実行するのではなく全索引スキャンを実行できる場合。
問合せで参照される列すべてが索引に含まれている。
問合せで参照される列の順序が先行する索引列の順序と一致している。
問合せには、索引内のすべての列、または索引内の列のサブセットを含めることができます。
GROUP BY句が問合せ内に存在し、GROUP BY句の列が索引内に存在する場合。列の順序を索引やGROUP BY句の順序と同じにする必要はありません。GROUP BY句には、索引内のすべての列、または索引内の列のサブセットを含めることができます。
高速全索引スキャンは、問合せに必要なすべての列が索引に含まれ、索引キー内の1つ以上の列にNOT NULL制約が存在する場合に、全表スキャンの代用として使用されます。高速全スキャンは、表にアクセスすることなく索引そのものに存在するデータにアクセスします。このスキャンでソート操作を解消できないのは、データが索引キーで並べられないためです。高速全スキャンでは、全索引スキャンとは異なりマルチブロックREADを使用して索引全体が読み取られ、パラレル実行も可能です。
初期化パラメータOPTIMIZER_FEATURES_ENABLEまたはINDEX_FFSヒントを使用して高速全索引スキャンを指定できます。高速全索引スキャンはビットマップ索引に対しては実行できません。
高速全スキャンは、表スキャンと同様にマルチブロックI/Oを使用してパラレル化できるため、通常の全索引スキャンより高速です。
|
注意: 索引のPARALLEL設定は、コスト計算に影響しません。 |
高速全索引スキャンには、特別な索引ヒントINDEX_FFSがあります。この形式と引数は、通常のINDEXヒントと同じです。INDEX_FFSヒントの詳細は、「アクセス・パスに関するヒント」を参照してください。
索引結合は、問合せで参照される表の列すべてが含まれている複数の索引のハッシュ結合です。索引結合が使用された場合は、関連するすべての列値が索引から取り出されるので、表アクセスは必要ありません。索引結合は、ソート操作の絞込みには使用できません。
クラスタ・スキャンは、索引クラスタに格納された表から、クラスタ・キー値の等しい行すべてを取得するときに使用されます。索引クラスタ内においては、同一のクラスタ・キー値を持つすべての行が同じデータ・ブロックに格納されています。クラスタ・スキャンを実行するために、Oracleは、クラスタ索引をスキャンすることによって、選択されている行のROWIDを最初に取得します。次に、Oracleはその行をROWIDに従って探します。
ハッシュ・スキャンは、ハッシュ値に基づいて行をハッシュ・クラスタに配置するために使用します。ハッシュ・クラスタ内においては、同一のハッシュ値を持つすべての行が同じデータ・ブロックに格納されています。ハッシュ・スキャンを実行するために、Oracleは、文によって指定されたクラスタ・キー値にハッシュ関数を適用することによって、最初にハッシュ値を取得します。次に、Oracleはそのハッシュ値を持つ行が含まれているデータ・ブロックを操作します。
サンプル表スキャンでは、単純な表、または結合およびビューを含む文などの複合SELECT文からデータのランダムなサンプルが取り出されます。このアクセス・パスは、文のFROM句にSAMPLE句またはSAMPLE BLOCK句が含まれているときに使用されます。SAMPLE句を持つ行単位でサンプリングするときにサンプル表スキャンを実行するには、表の中の指定されたパーセントの行を読み取ります。SAMPLE BLOCK句を持つブロック単位でサンプリングするときにサンプル表スキャンを実行するには、表のブロックの中の指定されたパーセントのブロックを読み取ります。
例11-6は、サンプル表スキャンを使用して、ブロックによるサンプリングを行ってemployees表の1%にアクセスします。
例11-6 サンプル表スキャン
SELECT *
FROM employees SAMPLE BLOCK (1);
この文のEXPLAIN PLAN出力は、次のような形式になります。
------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 68 | 3 (34)| | 1 | TABLE ACCESS SAMPLE | EMPLOYEES | 1 | 68 | 3 (34)| -------------------------------------------------------------------------
問合せオプティマイザは、次の要因に従ってアクセス・パスを選択します。
文で使用可能なアクセス・パス
各アクセス・パスまたはパスの組合せを使用して文を実行するための見積りコスト
アクセス・パスを選択する場合、オプティマイザは、文のWHERE句、およびFROM句の条件を調べて、使用可能なアクセス・パスを最初に判断します。次に、オプティマイザは、使用可能なアクセス・パスを使用して可能な実行計画のセットを生成し、文にアクセス可能な索引、列および表の統計を使用して各見積りコストを生成します。そして最後に、オプティマイザは見積りコストが最も少ない実行計画を選択します。
アクセス・パスを選択する場合、問合せオプティマイザは次の影響を受けます。
結合は、複数の表からデータを取り出す文です。結合はFROM句の中の複数の表で特性化され、各表の関係はWHERE句の中に結合条件を設定することで定義されます。結合では、片方の行セットが内部と呼ばれ、もう一方が外部と呼ばれます。
この項では、次の内容を説明します。
結合文に実行計画を選択するために、オプティマイザは、相互に関連する次の決定を行う必要があります。
アクセス・パス
単純な文では、オプティマイザは、結合文の各表からデータを取り出すアクセス・パスを選択する必要があります。
結合方法
行ソースの各ペアを結合するには、結合操作をOracleが実行する必要があります。結合方法には、ネステッド・ループ結合、ソート/マージ結合、デカルト結合およびハッシュ結合があります。
結合順序
3つ以上の表を結合する文を実行する場合、Oracleは2つの表を結合し、その結果作成された行ソースを次の表に結合します。このプロセスは、すべての表がその結果に結合されるまで続行されます。
実行計画を選択するとき、問合せオプティマイザでは次の点が考慮されます。
オプティマイザは、2つ以上の表を結合した結果を、最大1つの行を含む行ソースに限定するかどうかを最初に判断します。オプティマイザは、このような状況を表のUNIQUE制約およびPRIMARY KEY制約に基づいて認識します。このような状況が存在する場合、オプティマイザはこれらの表を結合順序の最初に並べます。その後で、残りの表の結合を最適化します。
外部結合条件を持つ結合文では、外部結合演算子のある表の結合順序は、条件内のその他の表の後にしてください。オプティマイザは、この規則に違反する結合順序を考慮しません。同様に、副問合せがアンチ結合またはセミ結合に変換されたときは、その副問合せからの表は、それらが接続または相互に関連付けされた外部問合せブロック内の表の後に置きます。ただし、ある環境では、ハッシュ・アンチ結合およびセミ結合はこの順序条件を上書きできます。
問合せオプティマイザでは、適用可能な結合順序、結合方法および使用可能なアクセス・パスに従ってオプティマイザが実行計画のセットを生成します。次に、オプティマイザは各計画のコストを見積り、コストが最も小さいものを選択します。オプティマイザは、次の方法でコストを見積ります。
ネステッド・ループ操作のコストは、外部表で選択されている各行およびその行に対する内部表の一致行をメモリーに読み取るコストが基準になっています。オプティマイザは、データ・ディクショナリ内の統計を使用してこれらのコストを見積ります。
ハッシュ結合のコストは、主に、結合への入力側の1つ上にハッシュ表を作成するコストと、それを調べるために結合のもう一方からの行を使用するコストに基づきます。
オプティマイザは、各操作のコストを判断するときにはその他の要因についても考慮します。たとえば、次のような場合があります。
ソート領域のサイズが小さいと、小さいソート領域内でのソートにCPU時間とI/Oがより多く消費されるため、ソート/マージ結合のコストが大きくなる傾向があります。SQL作業領域のサイズ設定の詳細は、「PGAメモリー管理」を参照してください。
マルチブロックREADカウントが大きいと、ネステッド・ループ結合に関してソート/マージ結合のコストが少なくなる傾向があります。多数の連続したブロックが単独のI/Oでディスクから読み取られる場合は、全表スキャンよりもパフォーマンスを改善するために、ネステッド・ループ結合の内部表についての索引が少なくなる傾向があります。マルチブロックREADカウントは、初期化パラメータDB_FILE_MULTIBLOCK_READ_COUNTによって指定されます。
問合せオプティマイザでは、ORDEREDヒントを使用して結合順序に関するオプティマイザの選択を上書きできます。ORDEREDヒントによって、外部結合に関するこの規則に違反する結合順序が指定された場合、オプティマイザはこのヒントを無視して順序を選択します。結合方法に関するオプティマイザの選択も、ヒントを使用して上書きできます。
ネステッド・ループ結合は、データの小さいサブセットを結合する場合や、結合条件が第2の表にアクセスする効率的な方法である場合に有効です。
内部表が外部表から（外部表によって）駆動されることを確認することが重要です。内部表のアクセス・パスが外部表とは独立している場合は、外部ループを繰り返すたびに同じ行が取得されます。これは、パフォーマンスをかなり低下させてしまいます。そのような場合は、2つの独立した行ソースを結合するハッシュ結合のほうがパフォーマンスが優れています。
ネステッド・ループ結合には、次のステップがあります。
オプティマイザで駆動表が決定され、これが外部表に指定されます。
その他の表は、内部表に指定します。
外部表にあるすべての行について、内部表にあるすべての行がアクセスされます。外部ループは外部表にあるすべての行に対するものであり、内部ループは内部表の中にあるすべての行に対するものです。次のように、外部ループは実行計画の内部ループの前に表示されます。
NESTED LOOPS outer_loop inner_loop
Oracle Database 11gリリース1（11.1）では、ネステッド・ループ結合の新規の実装が導入されています。そのため、ネステッド・ループを含む実行計画は、以前のリリースのOracle Databaseとは異なる状態で表示されます。Oracle Database 11gリリース1（11.1）では、ネステッド・ループ結合の新規の実装と従来の実装の両方が可能です。そのため実行計画を分析する場合は、NESTED LOOPS結合の行ソースの数が異なることを理解しておくことが重要です。
次の問合せについて考えます。
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE d.department_name IN ('Marketing', 'Sales')
AND e.department_id = d.department_id;
Oracle Database 11gリリース1（11.1）より前のリリースでは、この問合せの実行計画は次の実行計画のように表示されます。
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 19 | 722 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
この例では、結合の外部側はhr.departments表のスキャンで構成され、このスキャンによって、条件department_name IN ('Marketing', 'Sales')に一致する行が戻されます。内部ループにより、これらの部署に関連付けられている従業員がhr.employees表に取得されます。
Oracle Database 11gリリース1（11.1）では、ネステッド・ループ結合の新規の実装が導入され、物理I/Oの全体の待機時間が短縮されます。バッファ・キャッシュに存在しない索引または表ブロックが結合の処理に必要な場合、物理I/Oが必要となります。Oracle Database 11gリリース1（11.1）では、Oracle Databaseは一度に1つのリクエストを処理するのではなく、ベクターI/Oを使用して複数の物理I/Oリクエストをまとめて処理できます。ネステッド・ループ結合の新規の実装の一環として、NESTED LOOPS結合の2つの行ソースが実行計画に表示されます。以前のリリースでは1行のみが表示されていました。この場合、Oracle Databaseでは結合の外部側の表の値と内部側の索引とを結合するためにNESTED LOOPS結合の1つの行ソースを割り当てます。もう1つの行ソースは、最初の結合の結果を結合するために割り当てられます。これにより、索引に格納されている行IDが含まれ、結合の内部側に表が存在します。
「ネステッド・ループ結合の従来の実装」の問合せについて考えます。Oracle Database 11gリリース1（11.1）でネステッド・ループ結合の新規の実装を使用する場合、この問合せの実行計画は次の実行計画のように表示されます。
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 19 | 722 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
この場合、hr.departments表の行は、最初の結合の外部側で構成されます。最初の結合の内部側は索引emp_department_ixです。最初の結合の結果は、2番目の結合の外部側で構成されます。これにより、結合の内部側にhr.employees表が存在します。
結合のもう1つの行ソースが割り当てられない場合があります。この場合、実行計画は以前のリリースと同様に表示されます。次にこのような状況について説明します。
結合の内部側で必要なすべての列が索引に存在するため、表にアクセスする必要がない。この場合、Oracle Databaseでは結合の1つの行ソースのみが割り当てられます。
戻された行の順序が以前のリリースの順序と異なっている。したがって、Oracle Databaseで行の特定の順序を予約しようとする場合（たとえばORDER BYソートを行う必要がない場合など）、Oracle Databaseではネステッド・ループ結合の従来の実装が使用されます。
OPTIMIZER_FEATURES_ENABLE初期化パラメータが、Oracle Database 11gリリース1（11.1）以前のリリースに設定されている。この場合、Oracle Databaseではネステッド・ループ結合の従来の実装が使用されます。
オプティマイザは、2つの表の間で適切な駆動条件で少数の行を結合する場合に、ネステッド・ループ結合を使用します。外部ループから内部ループに起動するので、実行計画の中の表の順序が重要になります。
外部ループは駆動行ソースです。このループは、結合条件を駆動するための一連の行を生成します。行ソースは、索引スキャンまたは全表スキャンでアクセスされる表とすることができます。また、他の操作からでも行を生成できます。たとえば、ネステッド・ループ結合からの出力は別のネステッド・ループ結合の行ソースとして使用できます。
内部ループは外部ループから戻された行ごとに、索引スキャンによって反復されます。内部ループのアクセス・パスが外部ループに依存していない場合は、デカルト積で終了することが可能で、外部ループの反復ごとに、内部ループは同じ行セットを生成します。したがって、2つの独立した行ソースをまとめて結合する場合は、他の結合方法を使用することをお薦めします。
オプティマイザが他の結合方法を選択する場合は、USE_NL(table1 table2)ヒントを使用します。table1とtable2は、結合される表の別名です。
データが十分に小さいSQL例の場合は、オプティマイザは全表スキャンを優先してハッシュ結合を使用します。例11-7「ハッシュ結合」は、そのSQLの例です。ただし、USE_NLを追加してオプティマイザに指示し、結合方法をネステッド・ループに変更できます。USE_NLヒントの詳細は、「結合操作のヒント」を参照してください。
ハッシュ結合は、大きなデータ・セットを結合する場合に使用します。オプティマイザは、2つの表またはデータ・ソースの小さいほうを使用して、メモリー内の結合キーにハッシュ表を作成します。次に、大きいほうの表をスキャンし、ハッシュ表を調べて結合された行を見つけます。
この方法は、小さいほうの表が使用可能なメモリー内に収まる場合に最適です。これにより、コストが2つの表のデータに対する1回のリード・パスに制限されます。
オプティマイザは、2つの表が等価結合で結合され、次の条件のいずれかが真である場合に、ハッシュ結合で2つの表を結合します。
大量のデータを結合する必要がある。
小規模表の大きな部分を結合する必要がある。
例11-7では、表ordersがハッシュ表の作成に使用されます。また、後でスキャンされるorder_itemsは、これより大きな表です。
例11-7 ハッシュ結合
SELECT o.customer_id, l.unit_price * l.quantity
FROM orders o ,order_items l
WHERE l.order_id = o.order_id;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
|* 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("L"."ORDER_ID"="O"."ORDER_ID")
2つの表を結合するときに、ハッシュ結合を使用するようにオプティマイザに指示するには、USE_HASHヒントを適用します。SQL作業領域のサイズ設定の詳細は、「PGAメモリー管理」を参照してください。USE_HASHヒントの詳細は、「結合操作のヒント」を参照してください。
ソート/マージ結合を使用して、2つの独立したソースからの行を結合できます。ハッシュ結合は、一般に、ソート/マージ結合よりパフォーマンスが優れています。次の条件が2つとも存在する場合は、ハッシュ結合よりソート/マージ結合のほうがパフォーマンスの点で優れています。
行ソースはソート済。
ソート操作を終了する必要なし。
ソート/マージ結合に、より低速のアクセス方法（全表スキャンとは対照的な索引スキャン）の選択が含まれている場合、ソート/マージを使用する利点が失われる可能性があります。
ソート/マージ結合は、2つの表の間の結合条件が<、<=、>または>=などの等価条件ではない（ただし、非等価ではない）場合に有効です。ソート/マージ結合は、大きいデータ・セットの場合にネステッド・ループ結合よりパフォーマンスが優れています。等価条件がないかぎり、ハッシュ結合を使用できません。
マージ結合には、駆動表の概念はありません。結合には、2つのステップが含まれています。
ソート結合操作: 両方の入力が、結合キーでソートされる。
マージ結合処理: ソートされたリストがマージされる。
入力がすでに結合列でソートされている場合、その行ソースに対してソート結合操作は行われません。ただし、ソート/マージ結合では、最後の一致に戻ることができるように結合の右側に対して位置調整できるソート・バッファが常に作成されます。この場合、重複した結合キー値は結合の左側から取り出されます。
オプティマイザは、次の条件が真である場合に、ハッシュ結合よりソート/マージ結合を選択して大量のデータを結合します。
2つの表の間の結合条件が、等価結合ではない。
ソートが他の操作ですでに要求されているため、オプティマイザは、ハッシュ結合よりソート/マージを使用するほうがコストが低いと判断した。
ソート/マージ結合を使用するようオプティマイザに指示するには、USE_MERGEヒントを適用します。また、アクセス・パスを設定するためのヒントを与える必要がある場合があります。
USE_MERGEヒントでオプティマイザを上書きした方がよい場合があります。たとえば、オプティマイザは表に対する全体スキャンを選択して問合せ内でのソート操作を回避できます。ただし、大きい表は全表スキャンによる高速アクセスの場合とは異なり、索引および単一ブロック読取りでアクセスされるため、コストがかかります。
USE_MERGEヒントの詳細は、「結合操作のヒント」を参照してください。
他の表への結合条件を文中に持たない表が1つ以上ある場合に、デカルト結合が使用されます。オプティマイザは、データ・ソースにあるすべての行を、2つのセットのデカルト積を作成しながら、別のデータ・ソースにあるすべての行と結合します。
外部結合は単純結合の結果を拡張したものです。外部結合は、結合条件に一致するすべての行および結合条件が他の表のどの行とも一致しない、表の一部またはすべての行を戻します。
この操作は、外部結合が2つの表の間で使用されるときに使用します。外部結合は、内部（オプション）表に対応する行がない場合でも外部（保たれている）表の行を戻します。
正規の外部結合で、オプティマイザはコストに基づいて表（駆動する側と駆動される側）の順序を選択します。ただし、ネステッド・ループ外部結合では、表の順序は結合条件で決定されます。行が保たれる外部表は、内部表に駆動する場合に使用します。
オプティマイザは、ネステッド・ループ結合を使用し、次の状況で外部結合を処理します。
外部表から内部表まで起動できる。
データ量が少なく、ネステッド・ループ方法が効果的と判断できる。
ネステッド・ループ外部結合の例の場合は、USE_NLヒントを例11-8に追加して、ネステッド・ループを使用するようにオプティマイザに指示できます。たとえば、次のようにします。
SELECT /*+ USE_NL(c o) */ cust_last_name, sum(nvl2(o.customer_id,0,1)) "Count"
データ量が十分大きく、ハッシュ結合方法が有効と判断される場合や、外部表から内部表を駆動できない場合、オプティマイザは外部結合の処理にハッシュ結合を使用します。
表の順序はコストにより決定されます。外部表は、保存された行も含めて、ハッシュ表を作成する場合に使用されるか、またはハッシュ表を調べるときに使用される場合があります。
例11-8に、一般的なハッシュ結合外部結合の問合せを示します。この例では、与信限度が1000を超えるすべての顧客が問い合されます。外部結合は、オーダーを持たない顧客を見逃さないようにするために必要です。
例11-8 ハッシュ結合外部結合
SELECT cust_last_name, sum(nvl2(o.customer_id,0,1)) "Count"
FROM customers c, orders o
WHERE c.credit_limit > 1000
AND c.customer_id = o.customer_id(+)
GROUP BY cust_last_name;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 168 | 3192 | 6 (17)|
| 1 | HASH GROUP BY | | 168 | 3192 | 6 (17)|
|* 2 | NESTED LOOPS OUTER | | 260 | 4940 | 5 (0) |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 260 | 3900 | 5 (0) |
|* 4 | INDEX RANGE SCAN | ORD_CUSTOMER_IX | 105 | 420 | 0 (0) |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."CREDIT_LIMIT">1000)
4 - access("C"."CUSTOMER_ID"="0"."CUSTOMER_ID"(+))
filter("O"."CUSTOMER_ID"(+)>0)
この問合せは、様々な条件に一致する顧客を検索します。内部表に対応する行が見つからないと、外部結合は外部（保たれている）表の行とともに内部表の列に対してNULLを戻します。この操作で、orders行も持たないcustomers行がすべて検索されます。
この場合、外部結合条件は次のとおりです。
customers.customer_id = orders.customer_id(+)
この条件の構成要素を次に示します。
外部表はcustomersです。
内部表はordersです。
この結合は、orders内に対応する行を持たない行を含むcustomers行を保存します。
行を戻すには、NOT EXISTS副問合せを使用できます。ただし、ここでは表の全行の問合せを行っているため、ハッシュ結合のほうがパフォーマンスがよくなります（NOT EXISTS副問合せがネストされていない場合を除く）。
例11-9では、外部結合はマルチ表ビューに対して行われます。オプティマイザは通常の結合のようにビューを操作したり、述語をプッシュできないので、ビューの行セット全体を作成します。
例11-9 マルチ表ビューへの外部結合
SELECT c.cust_last_name, sum(revenue)
FROM customers c, v_orders o
WHERE c.credit_limit > 2000
AND o.customer_id(+) = c.customer_id
GROUP BY c.cust_last_name;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 144 | 4608 | 16 (32)|
| 1 | HASH GROUP BY | | 144 | 4608 | 16 (32)|
|* 2 | HASH JOIN OUTER | | 663 | 21216 | 15 (27)|
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 195 | 2925 | 6 (17)|
| 4 | VIEW | V_ORDERS | 665 | 11305 | |
| 5 | HASH GROUP BY | | 665 | 15960 | 9 (34)|
|* 6 | HASH JOIN | | 665 | 15960 | 8 (25)|
|* 7 | TABLE ACCESS FULL| ORDERS | 105 | 840 | 4 (25)|
| 8 | TABLE ACCESS FULL| ORDER_ITEMS | 665 | 10640 | 4 (25)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."CUSTOMER_ID"(+)="C"."CUSTOMER_ID")
3 - filter("C"."CREDIT_LIMIT">2000)
6 - access("O"."ORDER_ID"="L"."ORDER_ID")
7 - filter("O"."CUSTOMER_ID">0)
ビュー定義は、次のようになります。
CREATE OR REPLACE view v_orders AS
SELECT l.product_id, SUM(l.quantity*unit_price) revenue,
o.order_id, o.customer_id
FROM orders o, order_items l
WHERE o.order_id = l.order_id
GROUP BY l.product_id, o.order_id, o.customer_id;
外部結合で、外部（保たれている）表から内部（オプション）表への駆動ができない場合、外部結合ではハッシュ結合またはネステッド・ループ結合が使用できません。その場合、外部結合では結合操作を実行するためにソート/マージ外部結合を使用します。
オプティマイザは、次の場合に外部結合にソート/マージを使用します。
ネステッド・ループ結合の効率が悪い場合。データ量により、ネステッド・ループ結合は効率が悪い場合があります。
ソートが他の操作ですでに要求されているため、ハッシュ結合よりソート/マージを使用するほうがコストが低いと判断した場合。
完全外部結合は、左と右の外部結合の組合せのように動作します。内部結合では、内部結合の結果で戻されなかった両方の表からの行は保たれており、NULLで拡張されます。つまり、完全外部結合を使用すると、表をまとめて結合できますが、結合される表内に対応する行を持たない行も示すことができます。
例11-10の問合せでは、全部門と、その各部門に属する全社員を取得しますが、これには次の内容も含まれます。
部門に属さない全社員
社員のいない全部門
例11-10 完全外部結合
SELECT d.department_id, e.employee_id
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id
ORDER BY d.department_id;
文からは、次の出力が作成されます。
DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
10 200
20 201
20 202
30 114
30 115
30 116
...
270
280
178
207
125 rows selected.
Oracle Database 11gリリース1（11.1）以降では、Oracle Databaseによってハッシュ結合に基づくネイティブの実行方法が自動的に使用され、できるだけ完全外部結合が実行されます。新しい方法を使用して完全外部結合を実行する場合、問合せの実行計画にはHASH JOIN FULL OUTERが表示されます。例11-11に、例11-10の問合せの実行計画を示します。
例11-11 完全外部結合の実行計画
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 4758 | 6 (34)| 00:0 0:01 |
| 1 | SORT ORDER BY | | 122 | 4758 | 6 (34)| 00:0 0:01 |
| 2 | VIEW | VW_FOJ_0 | 122 | 4758 | 5 (20)| 00:0 0:01 |
|* 3 | HASH JOIN FULL OUTER | | 122 | 1342 | 5 (20)| 00:0 0:01 |
| 4 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | 108 | 2 (0)| 00:0 0:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 2 (0)| 00:0 0:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
HASH JOIN FULL OUTERが計画に含まれている点に注目してください。そのため、問合せではハッシュ完全外部結合の実行方法が使用されます。2つの表の完全外部結合条件が等価結合の場合、通常はハッシュ完全外部結合の実行方法が可能であるため、Oracle Databaseでは自動的にこの方法が使用されます。
ハッシュ完全外部結合の実行方法の使用を考慮するようにオプティマイザに指示するには、NATIVE_FULL_OUTER_JOINヒントを適用します。ハッシュ完全外部結合の実行方法の使用を考慮しないようにオプティマイザに指示するには、NO_NATIVE_FULL_OUTER_JOINヒントを適用します。NO_NATIVE_FULL_OUTER_JOINヒントでは、オプティマイザに対して、指定した各表を結合する際にネイティブの実行方法を除外するように指示します。かわりに、左外部結合と逆結合を組み合せたものとして完全外部結合が実行されます。
