Oracle10gでのSQLチューニング

今佳境のプロジェクトに突っ込まれていて、外部パートナーさんが作ったSQLをチューニングしているのですが、今回「え？そうなの？」と思ったことがあったのでメモ。どうせ次にやるときには、また忘れてるんで。。。(^^;
環境は

• Oracle 10g Release2 Standard Edition
• コストベースオプティマイザ
• ANALYZE TABLE ... SAMPLE 30 PERCENT

な感じです。

OR条件をREGEXP_LIKE関数に変更する

当然indexの状態や式などモノによっても変わりますが、今回「複数カラムの値に対する複数のOR条件」をREGEXP_LIKE関数に変更するとかなりコストが変わりました。以下で、valA、valB、valCは同じSQL内の別ストアド関数で算出された値で1〜5です。

■使用前
  WHERE (valA != '1' OR valB != '3' OR valC != '5')
  AND ...

■使用後
  WHERE REGEXP_LIKE(valA || valB || valC, '[^135]')
  AND ...

ちなみに、valA、valB、valCは、今後1〜5以外の値を取り得るので、[^135]としてます。確定しているのであれば、[24]で充分です。

INDEXが効かないカラムでのソートは早めに行う

■使用前
SELECT * FROM (
    SELECT
        some_function(...) AS a,
        ...
    FROM ...
    WHERE ...
)
WHERE ...
ORDER BY a, ...

のようなSQLではINDEXが使えないため、ソート句がボトルネックになる場合があります。この場合、AUTOTRACEなどのSQL統計でrecursive callsが非常に大きな値になっているハズです。今回はネストが4レベルぐらいのSQLでした。こういったSQLで以下のようにソート句を高レベル側に移動させる

■使用後
SELECT * FROM (
    SELECT
        some_function(...) AS a,
        ...
    FROM ...
    WHERE ...
    ORDER BY a, ...    /* ここに移動 */
)
WHERE ...

と、recursive callsが小さくなり、結果的にパフォーマンスが向上しました。

「すべてこうすればうまくいく」とは限らないのがSQLチューニングですが、遅いSQLが徐々に早くなるのは面白いですね。大変ですけど。。。
そういえば、Oracle9iを使っていた時にも思ったんですが、

• ルールベースの場合は「早いSQLの書き方」をする必要がある
• コストベースの場合「素直なSQL」の方が総じて早いSQLになる

ような気がします。これも「絶対」じゃありませんが。。。