Oracle 実用的で簡単なヒント句のつけかた
なんていう大それたタイトルをつけると
全然わかってねーだろ！と怒る人もいるかと思いますが…
（私はOracle Masterも持っていない人ですし）
なぜかSQLチューニングやらを仕事でやらされることが多く、
しかしチューニング関係の本やサイトは意味不明すぎて。
そんな中、実際にとんでもないＳＱＬとかをなんとか早くする為に
編み出したヒント句のつけかたを紹介します。
軽い気持ちで読んでください。
つけるべきヒント句は６種類だけ
だいたいがOracleに任せておけばいいんです。
ヒント句をがっぷりつけたところで状況が変わったら対応できなくなりますので。
私が主につけるヒント句は以下だけです。
テーブル結合に係る部分だけですね。
- ordered / leading
- inxex / full
- use_nl / use_hash
全部で６つです。これだけなら覚えられるでしょう。
他のは覚えきれないのでほっときましょう。
次からそれぞれ説明していきます。
ordered / leading
orderedはSQLで書いたテーブル順に結合します。
leadingは指定したテーブル順に結合します。
要はテーブル結合の順序を切り替えるわけですね。
なぜ必要かというと、A, B のテーブルをinner joinする際、
Aが100万件
Bが1件
の場合、
A, B の順で結合しても
B, A の順で結合しても
1件ですが
A, B の順で結合すると→一旦100万件取得してから1件になる
B,A の順で結合すると→1件取得して100万件の中の1件をさがして結合
という差が出てくるそうです。
つまり、結合順序は数が少ないものを先にしていくことが
重要ということです。
leadingは本体のSQLを書き換えなくても順序だけ変えられるので
俺のせいじゃないといえるところも大好きです。
use_nl/use_hash/full/index
結合方法を指定するヒント句です。
use_nlはネステッドループ結合をさせます。
元テーブル１件読んで、結合テーブルを探してくっつけて、次レコードへ。
を繰り返します。
use_hashはハッシュ結合をさせます。
元テーブル読んで、結合テーブル読んで、一気に結合します。
fullはテーブル全件を読み込ませます。
indexは指定したインデックスを使ってテーブルを抽出します。
この４つについては私の中で使い方に鉄則があるのでまとめました。
鉄則
- use_nl を使う場合、indexとセットで使う！
→この組み合わせは
主に同時アクセス数が多く
サーバ負荷を減らして実行させたい場合に使う。
オンライン処理とか。
あと、結合元１件に対して少量の結合の場合もこっち。 - use_hashを使う場合、fullとセットで使う！
→この組み合わせは
主に同時アクセス数が少なく
負荷かかってもいいから早さを優先する場合に使う。
夜間バッチとか。
あと、結合元１件に対して全件（近い件数）の結合の場合はこっち。
use_nlを使うと取得結果を１件ずつ結合するイメージになります。
(Nested Loop結合）
内部的には何度も結合することになるのでindex検索しないとですね。
Nested LoopでFULLしちゃってる場合、取得レコードの掛け算分結合に
なるのでおそーくなるというわけです。
反対にuse_hashは一回読み込んだものをメモリ（ということにしておく）に
展開して一気に結合します。
この時リソースは消費しますが結合元・結合先の読み込みは１回、
あとはメモリ上でくっつけることになるので早くなります。
ヒント句の書く場所
最後にヒント句の書き方を確認しておきます。
間違えても効果がないだけでエラーにならないので慎重に！
まず、基本です
SELECT /*+ ここにヒント句を書く */
xxxxx,
xxxxx
FROM zzzz A
INNER JOIN yyyy B
ON A.xxxx = B.xxxx
サブクエリの場合も同じ要領で
SELECT /*+ ここに親のヒント句 */
( SELECT /*+ サブクエリのヒント句 */ FROM zzzz A INNER JOIN yyyy B )
FROM
( SELECT /*+ FROM句サブクエリの場合はここ */ from zzzz A INNER JOIN yyyy B ) C
INNER JOIN
( SELECT /*+ FROM句サブクエリの場合はここ */ from zzzz A INNER JOIN yyyy B ) D
ヒント句の書き方
/*+ XXXXXXXXXXXX */
の形式で書きます。
上で書いたヒント句の例を記載します。
/*+ ordered USE_NL(A B) INDEX(A AINDEXNAME) INDEX(B BINDEXNAME) */
ヒント句で指定するテーブル名は実名でなくエイリアスです！これ重要。
ORDEREDはただ書けばそれでOKです。
INDEXヒントの場合、括弧の中にエイリアス名 ブランク インデックス名 を指定します。
INDEXヒントは１テーブルずつINDEXヒント句を分けて書いてくださいね。
/*+ LEADING(A B) USE_HASH(A B) FULL(A) FULL(B) */
leadingを使って use_hashとfullの場合です。
まとめ
というわけで６つのヒント句でなんとか今まで乗り切ってきました。
割と何とかなったのは
待ってくれるユーザの寛大さと
元のＳＱＬが案外優秀だった
ということにしておきます。
ここまで書いてなんですが
チューニングのスペシャリストには
なれそうもない（なりたくもない・・・）ですね。