16 オプティマイザ・ヒントの使用方法

オプティマイザ・ヒントをSQL文で使用して実行計画を変更できます。ヒントを使用して、特定のアプローチの使用をオプティマイザに指示する方法を説明します。

この章には次の項があります。

• オプティマイザ・ヒントの理解

• ヒントの指定方法

• ビューでのヒントの使用方法

オプティマイザ・ヒントの理解

ヒントを使用することにより、通常オプティマイザによって行われる意思決定を行うことができます。アプリケーション設計者が、オプティマイザの認知しない、データに関する情報を把握している場合があります。ヒントは、特定の基準に基づいて特定の問合せ実行計画を選択するオプティマイザを指示する機構を備えています。

たとえば、ある問合せに対しては、特定の索引を選択する方がよい場合もあります。この情報に基づいて、アプリケーション設計者がオプティマイザよりも効率的に実行計画を選択できます。その場合は、ヒントを使用して、オプティマイザが最適な実行計画を使用するように指示できます。

注意: ヒントを使用すると、管理、チェックおよび制御する必要のあるコードが増えます。

ヒントの型

ヒントは、次の一般的な型に分類されます。

• 単一表

  単一表ヒントは、1つの表またはビュー上で指定します。単一表ヒントの例として、INDEXおよびUSE_NLがあります。

• 複数表

  複数表ヒントは単一表ヒントに似ていますが、ヒントで1つ以上の表またはビューを指定できる点が異なります。複数表ヒントの例には、LEADINGがあります。USE_NL(table1 table2)は、複数表ヒントとはみなされないため注意してください。これは、実際にはUSE_NL(table1)およびUSE_NL(table2)のショートカットであるためです。

• 問合せブロック

  問合せブロック・ヒントでは、単一の問合せブロックが処理されます。問合せブロック・ヒントの例として、STAR_TRANSFORMATIONおよびUNNESTがあります。

• 文

  文ヒントはSQL文全体に適用されます。文ヒントの例には、ALL_ROWSがあります。

カテゴリ別のヒント

オプティマイザ・ヒントは次のカテゴリにグループ分けされます。

• 最適化アプローチと目標のヒント

• アクセス・パスに関するヒント

• 問合せの変換に関するヒント

• 結合順序のヒント

• 結合操作のヒント

• パラレル実行のヒント

• その他のヒント

このようなカテゴリと各カテゴリ内に含まれるヒントについては、次の各項で説明します。

関連項目: 構文と各ヒントの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

最適化アプローチと目標のヒント

次のヒントでは、最適化アプローチと目標のいずれかを選択できます。

• ALL_ROWS

• FIRST_ROWS(n)

最適化アプローチと目標を指定するヒントがSQL文に含まれている場合、オプティマイザは、統計の有無、OPTIMIZER_MODE初期化パラメータの値およびALTER SESSION文のOPTIMIZER_MODEパラメータにかかわらず、指定されたアプローチを使用します。

注意: オプティマイザの目標は直接実行される問合せにのみ適用されます。ヒントを使用して、PL/SQLの内部から実行されるSQL文のためのアクセス・パスを指定してください。ALTER SESSION...SET OPTIMIZER_MODE文は、PL/SQLの内部から実行されるSQLには作用しません。

SQL文にALL_ROWSヒントまたはFIRST_ROWS(n)ヒントを指定した場合、データ・ディクショナリ内に、文がアクセスする表に関する統計情報が作成されていないと、オプティマイザは、デフォルトの統計値（そのような表に割り当てられている記憶域など）を使用して、欠けている統計を見積ってから、実行計画を選択します。これらの見積りはDBMS_STATSパッケージによって生成された見積りほど正確ではありません。したがって、統計の収集にはDBMS_STATSパッケージを使用します。

ALL_ROWSヒントまたはFIRST_ROWS(n)ヒントとともに、アクセス・パスまたは結合操作のヒントを指定した場合、オプティマイザはヒントによって指定されたアクセス・パスと結合操作を優先します。

マージ可能なビューでのヒントの動作については、「ビューでの最適化アプローチと目標のヒント」を参照してください。

アクセス・パスに関するヒント

次の各ヒントでは、オプティマイザが表の特定のアクセス・パスを使用するように指示します。

• FULL

• CLUSTER

• HASH

• INDEX

• NO_INDEX

• INDEX_ASC

• INDEX_COMBINE

• INDEX_JOIN

• INDEX_DESC

• INDEX_FFS

• NO_INDEX_FFS

• INDEX_SS

• INDEX_SS_ASC

• INDEX_SS_DESC

• NO_INDEX_SS

これらのヒントの1つを指定すると、指定されたアクセス・パスが索引やクラスタの存在およびSQL文の構文構造体に基づいて使用できる場合のみ、オプティマイザはそのアクセス・パスを選択します。ヒントを使用できないアクセス・パスを指定すると、オプティマイザはその指定を無視します。

アクセスする表は、文に指定する場合と同じように正確に指定してください。文が表の別名を使用している場合、表の名前ではなく、表の別名をヒントで使用する必要があります。スキーマ名が文中にある場合は、ヒント内の表名にそのスキーマ名を入れないでください。

マージ可能なビューでのヒントの動作については、「ビューに対するアクセス・パスとヒント結合」および「ビューの内側のアクセス・パスとヒント結合」を参照してください。

注意: アクセス・パスのヒントの場合、SELECT文のFROM句でSAMPLEオプションを指定すると、Oracleはヒントを無視します。

関連項目: SAMPLEオプションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

問合せの変換に関するヒント

次の各ヒントでは、オプティマイザが特定のSQL問合せ変換を使用するように指示します。

• NO_QUERY_TRANSFORMATION

• USE_CONCAT

• NO_EXPAND

• REWRITE

• NO_REWRITE

• MERGE

• NO_MERGE

• STAR_TRANSFORMATION

• NO_STAR_TRANSFORMATION

• FACT

• NO_FACT

• UNNEST

• NO_UNNEST

結合順序のヒント

次のヒントは結合順序を指示します。

• LEADING

• ORDERED

結合操作のヒント

次の各ヒントでは、オプティマイザが表の特定の結合操作を使用するように指示します。

• USE_NL

• NO_USE_NL

• USE_NL_WITH_INDEX

• USE_MERGE

• NO_USE_MERGE

• USE_HASH

• NO_USE_HASH

USE_NLヒントとUSE_MERGEヒントは、結合順序のヒントとともに使用することをお薦めします。 「結合順序のヒント」を参照してください。Oracleでは、参照表が結合の内部表になった場合にこれらのヒントを使用し、参照表が外部表の場合にはこれらのヒントを無視します。

マージ可能なビューでのヒントの動作については、「ビューに対するアクセス・パスとヒント結合」および「ビューの内側のアクセス・パスとヒント結合」を参照してください。

パラレル実行のヒント

次のヒントでは、パラレル実行を行ったときに、文がどのようにパラレル化されるか、またはパラレル化されないかについてオプティマイザを指示します。

• PARALLEL

• PQ_DISTRIBUTE

• PARALLEL_INDEX

• NO_PARALLEL_INDEX

マージ可能なビューでのヒントの動作については、「ビューに対するパラレル実行ヒント」および「ビューの内側のパラレル実行ヒント」を参照してください。

関連項目: パラレル実行の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

その他のヒント

この項ではその他のいくつかのヒントを説明します。

• APPEND

• NOAPPEND

• CACHE

• NOCACHE

• PUSH_PRED

• NO_PUSH_PRED

• PUSH_SUBQ

• NO_PUSH_SUBQ

• QB_NAME

• CURSOR_SHARING_EXACT

• DRIVING_SITE

• DYNAMIC_SAMPLING

• MODEL_MIN_ANALYSIS

ヒントの指定方法

ヒントは、それらが含まれるSQL文ブロックの最適化のみに適用されます。文ブロックは、次のいずれかの文または文の一部です。

• 単純なSELECT文、UPDATE文またはDELETE文

• 複合文の親文または副問合せ

• 複合問合せの一部

たとえば、UNION演算子で結合した2つの問合せから構成されている複合問合せには、各構成要素の問合せに対して1つずつ、合計2つのブロックがあります。このため、この最初の構成要素の問合せにおけるヒントはその最適化のみに適用され、2番目の構成要素の問合せの最適化には適用されません。

次の各項では、ヒントの使用方法をさらに詳しく説明します。

• ヒント全セットの指定方法

• ヒントにおける問合せブロックの指定方法

• グローバル表のヒントの指定方法

• 複合索引ヒントの指定方法

ヒント全セットの指定方法

ヒントを使用するとき、ある状況では、最適な実行計画を確認するためにヒントの全セットの指定が必要な場合があります。たとえば、多数の表が結合された非常に複雑な問合せが存在するときに、指定の表に対してINDEXヒントのみを指定すると、オプティマイザは、使用される残りのアクセス・パスとともに、対応する結合方法も判断する必要があります。したがって、INDEXヒントを指定しても、オプティマイザによってそのヒントが使用されるとはかぎりません。これは、オプティマイザの選択した結合方法およびアクセス・パスによっては、要求された索引を使用できないとオプティマイザが判断するためです。

例16-1では、LEADINGヒントにより、使用する正確な結合順序が、別の表で使用される結合方法とともに指定されています。

例 16-1    ヒント全セットの指定方法

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) 
           USE_MERGE(j) FULL(j) */
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
  FROM employees e1, employees e2, job_history j
  WHERE e1.employee_id = e2.manager_id
    AND e1.employee_id = j.employee_id
    AND e1.hire_date = j.start_date
  GROUP BY e1.first_name, e1.last_name, j.job_id
  ORDER BY total_sal;

ヒントにおける問合せブロックの指定方法

問合せ内の問合せブロックを識別するには、ヒントにオプションの問合せブロック名を使用して、ヒントの適用先となる問合せブロックを指定します。問合せブロック引数の構文のフォームは@queryblockです。queryblockは、問合せ内の問合せブロックを指定する識別子です。queryblock識別子は、システム生成またはユーザー指定のいずれかとなります。

• システム生成識別子を取得するには、問合せにEXPLAIN PLANを使用します。変換前の問合せブロック名を決定するには、NO_QUERY_TRANSFORMATIONヒントを使用して、問合せにEXPLAIN PLANを実行します。

• ユーザー指定の名前は、QB_NAMEヒントにより設定できます。

例16-2では、ビュー上のSELECT文内の問合せブロックを指定するため、問合せブロック名がNO_UNNESTヒントとともに使用されています。

例 16-2    ヒントにおける問合せブロックの使用方法

CREATE OR REPLACE VIEW v AS
  SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
    FROM employees e1,
      ( SELECT *
          FROM employees e3) e2, job_history j
         WHERE e1.employee_id = e2.manager_id
          AND e1.employee_id = j.employee_id
          AND e1.hire_date = j.start_date
          AND e1.salary = ( SELECT max(e2.salary) 
                             FROM employees e2 
                             WHERE e2.department_id = e1.department_id ) 
  GROUP BY e1.first_name, e1.last_name, j.job_id
  ORDER BY total_sal;

問合せにEXPLAIN PLANを実行し、PLAN TABLE出力を表示した後、システム生成の問合せブロック識別子を決定できます。たとえば、問合せブロック名は次のPLAN TABLE出力に表示されます。

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'SERIAL'));
...
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
...
  10 - SEL$4        / E2@SEL$4

問合せブロック名が決定された後は、これを次のSQL文に使用できます。

SELECT /*+ NO_UNNEST( @SEL$4 ) */
  *
 FROM v;

グローバル表のヒントの指定方法

表を指定するヒントは、一般に、ヒントが呼び出される場所であるDELETE、SELECTまたはUPDATE問合せブロック内の表を参照します。文によって参照されるビュー内の表は参照しません。ビュー内に表示される表のヒントを指定する場合は、ビューに埋め込まれているヒントではなくグローバル・ヒントを使用することをお薦めします。この章で説明する表ヒントは、表の名前とともにビューの名前が含まれる拡張tablespec構文を使用して、グローバル・ヒントに変換できます。

また、tablespec構文の前にオプションの問合せブロック名を使用できます。「ヒントにおける問合せブロックの指定方法」を参照してください。

表を指定するヒントには、次の構文を使用します。

tablespec::=

画像の説明

各項目は次のとおりです。

• viewは、ビュー名を指定します。

• tableは、表の名前または別名を指定します。

ビューのパスが指定されている場合、ヒントは左から右へ解決されます。この場合、1つ目のビューはFROM句に含まれる必要があり、それ以降の各ビューは、前のビューのFROM句で指定されている必要があります。

たとえば、例16-3では、部門内で給与が最高である各従業員について、従業員の姓名、従業員の最初のジョブおよび従業員の全ダイレクト・レポートの合計給与を戻すためのビューvが作成されます。データを問合せる場合、ビューe2の表e3に索引emp_job_ixを使用することを強制できます。

例 16-3    グローバル・ヒントの使用例

CREATE OR REPLACE VIEW v AS
SELECT
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
        ( SELECT *
          FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
     AND e1.employee_id = j.employee_id
     AND e1.hire_date = j.start_date
     AND e1.salary = ( SELECT
                       max(e2.salary)
                       FROM employees e2
                       WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

グローバル・ヒント構造を使用することで、ビューe2の本体に索引ヒントを指定して、ビューvの変更を防ぐことができます。表e3に索引emp_job_ixを使用するよう強制するには、次のいずれかの文を使用します。

SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  * 
  FROM v;

SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ * 
  FROM v;

SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ * 
  FROM v;

グローバル・ヒント構文は、例16-4に示すように、マージ不可能なビューにも適用されます。

例 16-4    NO_MERGEを含むグローバル・ヒントの使用方法

CREATE OR REPLACE VIEW v1 AS
  SELECT *
    FROM employees
    WHERE employee_id < 150;

CREATE OR REPLACE VIEW v2 AS
  SELECT v1.employee_id employee_id, departments.department_id department_id
    FROM v1, departments
    WHERE v1.department_id = departments.department_id;

SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
                        FULL(v2.departments) */ *
  FROM v2
  WHERE department_id = 30;

このヒントは、v2をマージ不可能にし、従業員および部門表のアクセス・パス・ヒントを指定します。これらのヒントは、マージされていないビューv2にプッシュされます。

関連項目: 「ビューでのヒントの使用方法」

複合索引ヒントの指定方法

索引を指定するヒントには、次のように、単純な索引名またはカッコで括られた列のリストのいずれかを使用できます。

indexspec::=

画像の説明

各項目は次のとおりです。

• tableは、表の名前を指定します。

• columnは、指定された表内の列名を指定します。
  • オプションで列の前に表修飾子を付けることができるため、ヒントにより、索引列が索引付きの表とは別の表にあるビットマップ結合索引を指定できます。表修飾子がある場合、問合せに含まれる別名ではなく、実表である必要があります。

  • 索引指定の各列は、式ではなく、指定された表のベース列である必要があります。索引指定で指定された列がファンクション索引の接頭辞を形成している場合を除き、列指定を使用してファンクション索引をヒントで指定することはできません。

• indexは、索引名を指定します。

ヒントは次のように解決されます。

• 索引名が指定されている場合、その索引のみが考慮されます。

• 列リストが指定されており、列の数と順序が指定の列と一致する索引が存在する場合、その索引のみが考慮されます。このような索引が存在しない場合、指定された列が接頭辞として指定順序どおりに含まれる、表に対する索引が考慮されます。いずれの場合も、一致するすべての索引に対してユーザーが個別に同じヒントを指定した場合と同じ動作になります。

たとえば、例16-3では、job_history表に、employee_id列に対する単一列索引と、employee_idおよびstart_date列に対する連結索引があります。索引の使用に関してオプティマイザを明確に指示するには、次のように問合せにヒントを指定します。

SELECT /*+ INDEX(v.j jhist_employee_ix (employee_id start_date)) */ * FROM v;

ビューでのヒントの使用方法

ビュー（または副問合せ）内、またはビューに対してのヒントの使用は、お薦めしません。これは、1つのコンテキストに定義したビューを他のコンテキストでも使用できるためです。また、このようなヒントによって予想外の実行計画が発生する可能性があります。特に、ビュー内のヒントまたはビューに対するヒントは、そのビューがトップレベルの問合せにマージ可能かどうかによって処理方法が異なります。

表のヒントをビューまたは副問合せ内で指定する場合は、グローバル・ヒント構文の使用をお薦めします。「グローバル表のヒントの指定方法」を参照してください。

それでも、ビューでヒントを使用する場合は、この後の項で状況ごとの動作についての説明を参照してください。

• ヒントおよび複合ビュー

• ヒントとマージ可能ビュー

• ヒントとマージ不可能ビュー

ヒントおよび複合ビュー

デフォルトでは、複合ビューでヒントは使用できません。たとえば、複合ビューに対して選択する問合せでヒントを指定しても、そのヒントは機能しません。これは、ビュー内にヒントがプッシュされないためです。

注意: ビューが単一表の場合、ヒントは伝播されません。

ヒントがベース・ビュー内に存在しないかぎり、ビューに対する問合せからヒントが機能することはありません。

ヒントとマージ可能ビュー

この項では、マージ可能なビューでのヒントの動作について説明します。

ビューでの最適化アプローチと目標のヒント

最適化アプローチと目標のヒントは、トップレベルの問合せまたはビューの内側に指定できます。

• そのようなヒントがトップレベルの問合せにある場合は、ビューの内側にあるヒントとは関係なくそのヒントが使用されます。

• トップレベルのオプティマイザ・モード・ヒントがない場合は、参照されているビューのすべてのモード・ヒントに一貫性があるかぎり、それらのモード・ヒントが使用されます。

• 参照されているビューの2つ以上のモード・ヒントが矛盾する場合は、そのビューのすべてのモード・ヒントが廃棄されて、セッション・モードが使用されます（デフォルトかユーザー指定かには関係しません）。

ビューに対するアクセス・パスとヒント結合

参照されるビューに対するアクセス・パスとヒント結合は、そのビューが単一の表を含んでいないかぎり（または単一の表を持つその他のヒント・ビューを参照していないかぎり）無視されます。そのような単一表ビューでは、ビューに対するアクセス・パスやヒント結合は、そのビューの中の表に対して適用されます。

ビューの内側のアクセス・パスとヒント結合

アクセス・パスとヒント結合は、ビュー定義に含めることができます。

• ビューがインライン・ビューである場合（つまり、ビューがSELECT文のFROM句にある場合）、そのビューの内側のすべてのアクセス・パスとヒント結合は、そのビューがトップレベルの問合せにマージされるときに保存されます。

• インライン・ビューでないビューでは、そのビューの中のアクセス・パスとヒント結合が保存されるのは、参照問合せが他の表やビューを参照していない場合（つまり、SELECT文のFROM句にそのビューしか含まれていない場合）のみです。

ビューに対するパラレル実行ヒント

ビューに対するPARALLEL、NO_PARALLEL、PARALLEL_INDEXおよびNO_PARALLEL_INDEXヒントは、参照されるビュー内のすべての表に繰り返し適用されます。トップレベルの問合せのパラレル実行ヒントは、参照されるビューの内側のそのようなヒントを上書きします。

ビューの内側のパラレル実行ヒント

ビューの内側のPARALLEL、NO_PARALLEL、PARALLEL_INDEXおよびNO_PARALLEL_INDEXヒントは、ビューがトップレベルの問合せにマージされるときに保存されます。トップレベルの問合せにあるビューのパラレル実行ヒントは、参照されるビューの内側のそのようなヒントを上書きします。

ヒントとマージ不可能ビュー

マージ不可能なビューでは、ビューの内側の最適化アプローチと目標のヒントは無視されます。つまり、トップレベルの問合せにより最適化モードが決定されます。

マージ不可能なビューはトップレベルの問合せとは別に最適化されるので、そのビューの内側のアクセス・パスとヒント結合は保存されます。同じ理由から、トップレベルの問合せ内のビューに対するアクセス・パスも無視されます。

ただし、トップレベルの問合せ内のビューに対するヒント結合は保存されます。この場合、マージ不可能なビューは表と同様であるためです。