kagamihogeの日記

kagamihogeの日記です。

サブクエリの条件が悪くメインクエリがフルスキャンになる

DB SQL Oracle プログラミング失敗談

環境

起きたこととか

下記のようなSQLを実行したところ、ちょっと表現が難しいくらい猛烈な実行時間がかかってしまった。

UPDATE BILLING B
   SET AMOUNT = 0
 WHERE B.BILLING_ID = (SELECT O.BILLING_ID
                         FROM ORDERS O
                        WHERE O.BILLING_ID = B.BILLING_ID 
                          AND O.HOGE_DATE > TO_DATE('2013/03/01', 'yyyy/mm/dd')
);

やろうとしていたことは、注文テーブルからサブクエリである条件を満たす請求IDを抽出してその請求金額を更新する。状況を単純化するため、サブクエリは1件だけ返ってくるものとしておく。また、実際にはサブクエリの条件ももっと複雑だし、AMOUNT = 0とかもアレだけど、細かいところは無視して頂きたい。

一見すると特に問題が無さそうな感じだが、実行計画を見るとかなりおかしい。下記の図を参照。

確実におかしいのは、メインクエリ側のBILLINGをフルスキャンしてしまっている点。本番環境の請求テーブルの件数は、具体的な件数を書くことは出来ないが相当な分量があるため、これをフルスキャンしたら猛烈な時間がかかるのは当然である。サブクエリで抽出される請求IDは1件ということにしているので、どうみてもフルスキャンはおかしい、というかマズイ。

ということで、上記のようなクエリを書きたい場合は下記のようにするのが望ましい。

UPDATE BILLING B
   SET AMOUNT = 0
 WHERE B.BILLING_ID = (SELECT O.BILLING_ID
                         FROM ORDERS O
                        WHERE 
                              O.HOGE_DATE > TO_DATE('2013/03/01', 'yyyy/mm/dd')
);

実行計画はこんな感じ。

請求テーブルのインデックスをユニークスキャンするようになったので、これはほんの僅かな時間で終了する。

なぜこのような現象が発生するのか？　原因は、サブクエリの条件にメインクエリの列が含まれているため、サブクエリだけを個別に実行することが論理的に出来なくなってしまった（少なくとも、この環境のOracleのオプティマイザはそう判断した）　そのため、メインクエリを先に実行せざるを得ず、メインクエリ側の請求テーブルはフルスキャンを選択してしまっている。

これを解消するには、O.BILLING_ID = B.BILLING_ID を無くす。サブクエリとの結合条件に当たる部分は、WHERE B.BILLING_ID = (SELECT O.BILLING_IDによって書かれているので、そもそもこの条件文自体が不要である。

開発者用のテスト用データベースには本番と同等のデータは投入されておらず、実際に実行してみたときに強烈な遅いクエリとなることに気付かなかったのも痛かった。

　なくてもよいものを挿入して、常にサブクエリが後で実行するように作成されるためである。もし、これがなければ、状況によってオプティマイザは、メインクエリを先に実行することもでき、サブクエリを先に実行することもできるので、より有利な方向を選択できる柔軟性をもつようになる。
　（中略）
　できる限り多くの条件が「＝」となるほどよい、という漠然とした期待から生じる誤りである。
　（中略）
　漠然とした不安な根拠の無い期待のみをもってむやみにSQLを作成すると、驚くべき違いをもたらすことがあるという点に注意してほしい。

データベースパフォーマンスアップの教科書 基本原理編 P.452〜p.453 第6章 ジョインの最適化 6.3.5.2 セミジョインの実行計画 より抜粋

今回はまさしくその通りの、手痛い経験をすることとなった。