皆さんこんにちは、今年の夏は台風が多く天気が変わりやすい夏ですね。今月になっても雨が多く昨年までのような暑さはないので、このまま秋になってしまいそうですね。
今回は、オプティマイザに実行計画の処理を指示することができる、オプティマイザ・ヒント（ヒント）の使い方について説明しようと思います。ヒントは、この連載でも何度か紹介していますが、使い方は慣れないと難しいところがありますので、参考にしてください。
1. ヒントの指定
オプティマイザ統計が正しくないなどで、最適な実行計画にならない場合があります。そのとき実行計画を補正するためにヒントを使用するので、使い方を知っておくのはSQLチューニングでは重要です。ただし、ヒントは、指定に矛盾などがあると無視されてしまうので、指定が正しいのかを判断するのが難しいところがあります。特に、ビューや副問合せなどが存在するときに、表を指定するヒント（単一表ヒント、複数表ヒント）の指定方法は難しいです。また、指定が正しくないときでも、エラーなどが出力される訳ではないので、試行錯誤で指定している場合や、使用をあきらめている場合も多いかと思います。そのような方のために、以下の三つの指定方法について、注意点などを含めて基本的なことをまとめてみました。
- 通常ヒント
- 問合せブロック付きヒント
- グローバル・ヒント
まずは、INDEXヒントやNO_INDEXヒントなどの単一表ヒント（一つの表だけを指定するヒント）について、それぞれを説明していきます。
（１）通常ヒント
通常ヒントは、単純で最も多く使用する方法ですが、自身の問合せブロック内だけで使用する必要があります。そのため、副問合せなどが存在するときには、主問合せから指定することはできません。副問合せに指定するときには、以下のように副問合せ側の問合せブロックにヒントを設定する必要があります（以下の例は、INDEXヒントを主問合せからと、副問合せから指定する場合になります）。また、このように表別名を指定しているときは、ヒントにも表別名を使用するのを忘れないでください。
分かりやすいように、以下の簡単なビュー’v1’を作成して、その中の表にヒントを使用してみます。以下のSELECT文は、ビュー’v1’内で索引アクセスを全表スキャンにするために、主問合せからNO_INDEXヒントを行っていますが、効果がないことが分かります（NO_MERGEヒントは、ビュー・マージさせないように使用しています）。
ビューでは、ヒントを追加するのが簡単ではないので（CREATE VIEW文でヒントを指定することはお薦めしないので）、主問合せから指定することが多いと思います。そのため、この指定ではビューに使用するのは難しいということです。
（２）問合せブロック付きヒント
問合せブロック付きヒントは、指定したい問合せブロックに対して、明示的にヒントを指定する方法です。表を指定するヒントでは、以下のオプションの問合せブロック名を指定して、ヒントが適用される問合せブロックを指定することができます。
この指定では、副問合せなどの問合せブロック名を指定することで、主問合せからもヒントを指定することができます。そのため、通常ヒントよりもビューに対してヒントを指定することが簡単です。 この問合せブロック名には、システム生成とユーザ指定のものがあるので、様々なところで使うことができます。
・ システム生成
システムで自動的に生成する識別子ですが、デフォルトの実行計画には出力されません（dbms_xplan.display_cursorなどのFORMATパラメータに’ALIAS’を指定することで、’Query Block Name’が出力されて確認できます）。以下は、先程のSQLをこのパラメータの指定で出力した実行計画の一部になります。
この場合は、問合せブロック名が’SEL$2’になるので、以下のようにそれを指定すると、ヒントが有効になり全表スキャンになっています。
・ ユーザ指定
QB_NAMEヒントを使用して、ユーザ指定の問合せブロック名を設定することもできます。以下の例は、QB_NAMEヒントを指定するために、上記のSQLのビュー’v1’をインライン・ビュー’A’に変更して行っています（この例では、問合せブロック名に’Q1’を指定しているので、’Query Block Name’に’Q1’が出力されています）。インライン・ビューなどのような、問合せブロック名を指定できるときはこちらの方が、どこに指定しているか分かりやすいと思います（複雑なSQLでは、同じ表を複数の副問合せで使用する場合もあります）。
（３）グローバル・ヒント
グローバル・ヒントは、ヒントの表指定を'<ビュー名>.<表名>’の形式で行います。そのため、ビューにヒントを指定する場合は、主問合せから最も簡単に指定することが可能です（インライン・ビューでも使用できます）。以下は、ビュー’v1’内で索引アクセスさせないために、NO_INDEXヒントをグローバル・ヒントで指定している例です。
ただし、以下のようなANSI準拠の結合文を使用したSQLでは使用できないので、そのようなSQLのときは「（２）問合せブロック付きヒント」を使用してください。
このように、単一表ヒントについてはそんなに難しくはないので、それぞれを正しく使い分けてください。
2. 複数表ヒント
次に、LEADINGヒントなどの複数表ヒントについて説明します。
複数表ヒントは、二つ以上の表またはビューを指定することができるヒントです。そのため、複数のビューや表を同時に指定するときもありますが、複数の問合せブロックを参照するグローバル・ヒントは指定できません（問合せブロック付きヒントでも指定できません）。例えば、以下のような通常ヒントとグローバル・ヒントが混在する指定はできません（ここからは、以下のビュー’v2’を使用して説明していきます）。
そのため、以下のように二つ（主問合せブロックとビュー’v2’の問合せブロック）に分けて指定する必要があります。
それから、ビューやインライン・ビューなどについては、問合せ変換（SQL自動変換）されてヒントが無効になる場合があるので、少し注意が必要です。単一表ヒントについては、問合せ変換しても無効にならないので、気にする必要はありません。そのため、問合せ変換（ビュー・マージ、ビュー作成）されたときの複数表ヒントについても説明しておきます。
（１）複数表ヒントとビュー・マージ
LEADINGヒントなどは、ビュー・マージされると無効になる場合があります。例えば、上記SQLがビュー・マージされると、以下の実行計画のようになります（これは、ビュー’v2’がなくなるので、LEADING(tab1 v2)ヒントが無効になるからです）。
また、明示的にビュー・マージを行った後に、すべての表に対して結合順序を指定したい場合があります。その場合は、一つのヒントで行う必要があります。例えば、以下のようにビュー・マージされた後に、ビュー内の表も含めて結合順序を指定したいというときです。ただし、通常ヒントとグローバル・ヒントが混在しているため、この指定は無視されます。
このようなときには、オブジェクト別名を使用します。オブジェクト別名もFORMATパラメータに’ALIAS’を指定することで、実行計画に’Object Alias’が出力され確認できます（以下は、上記SQLをこのパラメータで出力した実行計画の一部になります）。
以下のように表名をこのオブジェクト別名で指定します（ヒントが有効になり、結合順序が調整されています）。表’tab1’については、問合せブロック内の表になるので、別名でなくても問題ありません。
オブジェクト別名は、基本は'<表名>@<問合せブロック名>’になるので、インライン・ビューなどのときは以下のようにQB_NAMEヒントでユーザ指定の名前を使用することもできます。これだと事前に実行してオブジェクト別名を調べる必要もありません。以下のように’Object Alias’が変わっています。
（２）複数表ヒントとビュー作成
副問合せなどがないSQLでも、問合せ変換によってインライン・ビューを作成する場合もあります。その場合は、問合せ変換に影響するヒントは無視されます。例えば、ビューを作成する変換が行われた場合には、それに影響するLEADINGヒントは無視されてしまいます（ビューを作成すると、通常ヒントでは指定できなくなるからです）。以下の例は、表の結合順序をt01,t02とするようにヒントを指定していますが、ビュー’VW_GBC_1’が作成されたことで無効になっています（この例では、第29回で説明したPLACE_GROUP_BYヒントで自動的にビューを作成しています）。
この場合は、ビュー名を指定することで、LEADINGヒントを有効にできます（以下のように、作成されたビュー名をしています）。このように使用することはないと思いますが、知っておくといざというときに便利かと思い載せておきました。
このように問合せ変換されると、ヒントが無効になってしまう場合があるので、ヒントを指定するときにはビューなどに関係する問合せ変換はされないようにするか、または明示的に問合せ変換するときだけ使用するようにしてください。また、問合せ変換に注意する必要があるので、できればヒントを使用しなくても、最適な実行計画を作成するようにしてください。
結合操作ヒントについて
結合操作ヒントは、複数表ヒントと思っている方が多いようなので、ここで簡単に説明しておきます。
結合操作ヒントとは、USE_HASH（ハッシュ結合）、USE_NL（ネステッド・ループ結合）などの結合方法を指定するヒントですが、これは単一表ヒントになるので、結合順序は指定できません。そのため、結合順序も指定するときは、LEADINGヒントなどと同時に使用する必要があります。例えば、t1、t2の順番でネステッド・ループ結合する場合に、以下の①のように指定する方がいると思いますが、この指定は正しくありません（ただし、USE_NL(t1)とUSE_NL(t2)に分割して外部表（最初にアクセスする表）側を無視するので、結合順序は調整されないがネステッド・ループ結合は動作します。そのため、これが正しいと思っている方も多いと思います）。②のように’USE_NL(<内部表>)’と行うのが正しい指定になります（他の結合方法のときでも同じなので覚えておきましょう）。このようなことが、思ったように動作しない原因にもなるので注意してください。
① SELECT /*+ USE_NL(t1 t2) */ * FROM t1,t2 WHERE … ; ② SELECT /*+ LEADING (t1 t2) USE_NL(t2) */ * FROM t1,t2 WHERE … ;
それでは、複数表を指定する場合とは、三つ以上の表を結合のときに、以下のように簡略化するときなどに使用します（あまり使用することはないと思います）。
SELECT /*+ LEADING (t1 t2 t3) USE_NL(t2 t3) */ * FROM t1,t2,t3 WHERE … ;
3. おわりに
今回はオプティマイザ・ヒントについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。