Oracle RBOでハマったこと

9iの環境でちょっと盲点だったので忘れないようにアウトプットしておく。

まずSQL

SELECT cola, colb, colc, cold, cole, colf
FROM tab_a
WHERE cola = xx
AND colb = yy
AND ( colc = zz1
OR colc = zz2);

絞り込みの値はバインド変数で色々変わるかも、という状況。
そんでトレース結果。

Total count 3 cpu 0.5 elapsed 10 disc 10000 current 0 rows 10
Optimizer goal: CHOOSE

Rows Row Source Operation
------------------------------
0 CONCATENATION
0 TABLE ACCESS BY INDEX ROWID tab_a
8000 INDEX RANGE SCAN idx_1
0 TABLE ACCESS BY INDEX ROWID tab_a
25000 INDEX RANGE SCAN idx_1

索引情報はこんな感じ
idx_1 (colc)
idx_2 (cola, colb, colc, cold)


さて、普通に考えればidx_2を使ってくれれば良さそうなのに、idx_1を使ってくれちゃってるのが気になる木になる。
それとCONCATENATION。
コストベースな頭だとパパっと理解できなかった。

で、まずCONCATENATIONの原因としては、オプティマイザによるSQL変換。トランスレーションとか言うんだっけ？
こいつのせいっぽかった。
ORが含まれる条件は2つのSQLに分離してUNION ALLする形に変換されてるっぽい。
つまり、
SELECT …
FROM tab_a
WHERE cola = xx
AND colb = yy
AND colc = zz1
UNION ALL
SELECT …
FROM tab_a
WHERE cola = xx
AND colb = yy
AND colc = zz2
AND cola != xx
AND colb != yy
AND colc != zz1;
↑
重複排除のためにこんなんが追加されるっぽい。

そんなわけで、CONCATENATIONで2回scanが走っていると。
でも、idx_2を使ってくれれば1回で済むんじゃね？なんでidx_1を使うのかっていうところで悩んだのですが、これってルールベースのランキングが原因でした。
ランキング参考ページ
今回の変換後のSQLに対し、idx_2はランキング10。（coldが条件に含まれてないから）
それに対しidx_1はランキング9。
どう見てもidx_2の方がよさそうなのに、ランキングで言えばidx_2がよいと判断されてしまってるわけです。

idx_1を削除できればいいのですが、他のSQLで使用しているため、削除は不可。
かといって、（cola, colb, colc）のインデックスを追加するのも、idx_2と冗長なので不可。
と、いうことでインデックス側での対処は無理でした。

あとできることは、CBOに変えてしまうか、ヒント句でなんとかするか、もしくはそれ以外の何かを使うか。
CBOへの変更は運用計画にも影響がでるし。ただ、optimizer_modeがchooseなんで、この表だけ統計をとるってのはありかもしれない。

そんなわけで、
・indexヒントでidx_2を指定
・first_rowsヒントでidx_2を選んでくれるのに期待(統計を取らなくてもデフォルト値でそれなりに選択してくれる)
・first_rowsヒント＋no_expandヒントでsql変換防止
のどれかで対応をとることに。
indexヒントはメンテしにくくなるんでちょっと嫌われる傾向があるので、CBOの優秀さに期待して統計なしのfirst_rows＋no_expandが一応最適解のような。

あとはSQLでちょっと小細工してidx_1が選択されにくくするってのはあるかもしれないけど、ロジックが変わると再テスト、ヒントならテストなしってのが結構通りやすいので、そういった意味でもヒントが良さげ。

統計とっていいなら素直にCBOが一番なんだろうけどなー。
いまだにRBOオンリーの環境がちょいちょあって、運用計画とか今更変える気のないお客さんだと、色々大変だとかそんな話でした。