Works - オプティマイザヒントを使用したパフォーマンスチューニング

オプティマイザヒントとは、オプティマイザに対して、最適（※１）な実行計画を強制的に使わせるための機能（※２）です。
かつて、口さがない SQL Server 使いの間では「Oracle にあんなにヒントの種類があるのはオプティマイザがバカだからだ」と云ったご意見もありましたが、今や SQL Server のヒントもかなりの種類がありますｗ

最もよく使われるヒントのひとつに indexヒントがあります。
コストベースでカーディナリティも高いのに全表走査してしまう場合などに使われます。
以下のSQLを例に取ります。

select 
*
from kemp e inner join kdept d
on e.deptno = d.deptno;

アプリケーション開発者が期待する実行計画は、以下の様なものであるとします。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'KEMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'KDEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_KDEPT' (UNIQUE)

StmtText
  |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[deptno]))
       |--Clustered Index Scan(OBJECT:([misc].[dbo].[kemp].[PK_KEMP] AS [e]))
       |--Clustered Index Seek(OBJECT:([misc].[dbo].[kdept].[PK_KDEPT] AS [d]), SEEK:([d].[deptno]=[e].[deptno]) ORDERED FORWARD)

~~MSSQL では、このようにネステッドループ結合の場合であっても、片側フルスキャンにならないようです。~~

＜訂正＞どこに目を付けているんでしょうか、私。片側INDEXフルスキャンになっていますねｗ＜/訂正＞

つまり、kemp（社員テーブル）を全表走査、kdept（部署テーブル）をネステッドループ結合、結合時、kdeptの主キーを使用。これでスループットとしては、単純にkempを全表走査した場合と（ほぼ）同等になると思われます。
kempに対する検索条件を付加した時に、ユニークスキャン、またはレンジスキャンされれば、かなり高速なクエリーになるはずです。

しかし、現場ではそうはいかない時も多々あります。
以下の様になってしまった場合を考えてみましょう。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   HASH JOIN
   2    1     TABLE ACCESS (FULL) OF 'KEMP'
   3    1     TABLE ACCESS (FULL) OF 'KDEPT'

StmtText
  |--Hash Match(Inner Join, HASH:([e].[deptno])=([d].[deptno]), RESIDUAL:([d].[deptno]=[e].[deptno]))
       |--Clustered Index Scan(OBJECT:([misc].[dbo].[kemp].[PK_KEMP] AS [e]))
       |--Clustered Index Scan(OBJECT:([misc].[dbo].[kdept].[PK_KDEPT] AS [d]))

クエリーが複雑すぎるのかもしれないし、統計情報を取得していないからかもしれないし、原因は様々ですが、オプティマイザがハッシュ結合を選択してしまった場合です。
ハッシュ結合はパラレル処理などでは効果的ですが、今回の例などではBツリーインデックスを使って欲しいところです。

「そんな時は indexヒントだ」と言う事で以下のようなヒントを指定したとします。

select 
/*+
index(d PK_KDEPT)
*/
*
from kemp e inner join kdept d
on e.deptno = d.deptno;

select *
from kemp e inner join kdept d WITH(INDEX(PK_KDEPT))
on e.deptno = d.deptno

オラクルのヒントのカッコの中は以下のようになります。

index([テーブル名] [オブジェクト名])

クエリー内でテーブルを別名（エイリアス）で指定している場合には、ここで指定するテーブル名も別名である必要があります。

さて、これで、期待したプランになれば万々歳ですね。
このコラムはここで終わりですｗ
（実際、この対処で効果がある場合も多いのです）

ところが人生は川のようなもの。
「まったく変わらない」（または「索引でフルスキャン」？）になってしまう場合もあります。

「どの程度のカーディナリティで索引走査をするか」の閾値は、RDBMS やオプティマイザのバージョンにより異なりますが、この場合、PK_KDEPT は主キーですからカーディナリティに問題があるとは思えません。
とにかく奴（オプティマイザ）は「ここはハッシュ結合だろ」と意固地に考えてしまっているわけです。こいつを止めさせたい。

つまり「いや、そーじゃないだろ。ネステッドループにして、さらに索引も使ってくれ」と言ってやればいいわけです。どのテーブルを駆動表としたいか、も言い添えて。

select 
/*+ 
ordered
use_nl(d)
index(d PK_KDEPT)
*/
*
from kemp e inner join kdept d
on e.deptno = d.deptno;

select *
from kemp e inner loop join kdept d WITH(INDEX(PK_KDEPT))
on e.deptno = d.deptno;

実行計画はこうなりました。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=82 Bytes=7790)
   1    0   NESTED LOOPS (Cost=84 Card=82 Bytes=7790)
   2    1     TABLE ACCESS (FULL) OF 'KEMP' (Cost=2 Card=82 Bytes=4920)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'KDEPT' (Cost=1 Card=1 Bytes=35)
   4    3       INDEX (UNIQUE SCAN) OF 'PK_KDEPT' (UNIQUE)

StmtText
  |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[deptno]))
       |--Clustered Index Scan(OBJECT:([misc].[dbo].[kemp].[PK_KEMP] AS [e]))
       |--Clustered Index Seek(OBJECT:([misc].[dbo].[kdept].[PK_KDEPT] AS [d]), SEEK:([d].[deptno]=[e].[deptno]) ORDERED FORWARD)

ここではある程度フルフルで書きましたが、オラクルなら ordered , use_nl を指定するだけでも、オプティマイザは「じゃ、索引は PK_KDEPT を使えばいいかな」と言うところまで補完してくれる場合が多いと思います。
MSSQLの場合は、loopの変わりにremoteを指定しても、オプティマイザがネステッドループを補完してくれると思います。

このネステッドループを強制する小技は、かなり以前に、某社コンサルチームが使っていたのを見て知った方法です。当時はアンドキュメンテッドに近いものと思われたので、公開を控えていましたが、最近では、ガイ･ハリソンのSQLチューニング本（※３）に明記されていますし、また、 OTN の回答や＠IT のコラムなどでも見かけるようになり、少なくともアンドキュメンテッドなものでは全くない、という感じになっていますので、私も安心して書きましたｗ

気をつけていただきたいのは、「どんな場合でもヒントを使えば思うがままの実行計画を強制できるわけではない」と言う事です。コストベースかルールベースか、でも結果は変わりますし、カーディナリティが極めて低いのにBツリーのインデックスを使わせようとしても無理がある、と言うものです。
言うまでもなく、開発環境で「ヒントが効いた」からと言って、本番環境に当てて同じ結果が得られるわけでもありません。

また、ヒントが有効に機能した状態であっても、移行やメンテナンスにより、そのヒントが付与されている事で、却って最適でない実行計画のままシステムが動き続けると言う可能性も孕んでいます。
例えば、Oracleのヒントは単なるコメントなので、エラーになりません。indexヒントが効いている状態で、もし索引名が変わったらオプティマイザのバージョンによっては、存在しない索引を探して、結局フルスキャンなんて場合も考えられます。（←未検証ですが）

このように、言わば諸刃の剣のようなものである事に留意していれば、ヒントは、パフォーマンスチューニングにおいて極めて有効な手段となり得ます。

[逆引きSQL比較:ORACLE] INDEXを使わせるには？（オプティマイザヒントを使用した実行計画の強制）
[逆引きSQL比較:MSSQL] INDEXを使わせるには？（オプティマイザヒントを使用した実行計画の強制）
[逆引きSQL比較:共通事項] 複雑なSQLを書くのは結構楽しい。…だが！
[コラム] オプティマイザヒントを使用したパフォーマンスチューニング－２

※１最適な実行計画
オプティマイザが選択する実行計画は本来「最適」であるはずなので、ここで言う「最適」とは、「そのSQLを発行したアプリケーションにとって＝人間にとって」最適かどうか、と言う事です。

※２実行計画を強制的に使わせる
Oracleにはヒント以外にも、「最適な状態の実行計画を保存・再使用」するための「ストアド・アウトライン」と言う機能があります。

※３ガイ･ハリソンのSQLチューニング本
「OracleSQLチューニング―オラクルの潜在能力を引き出す実践的チューニングガイド」
http://www.amazon.co.jp/exec/obidos/ASIN/4894711494/250-1958961-8328231
↑
必読。因みに訳者は、Seasarプロジェクトの比嘉康雄（ひがやすを）さん。

■よく使われる indexヒントの例

■indexヒントが効かなかった時の対処の１例

■留意点

■関連記事

■注釈