ALL_ROWS 最高のスループットとなるように最適化される（全表スキャン、ソート／マージ結合が選択されやすくなる）
FIRST_ROWS(n) レスポンスタイムを最短にするように最適化される（索引スキャンとネステッド・ループ結合が選択されやすくなる）Oracle SQLチューニング講座（3）：SQLチューニングの必須知識を総ざらい（後編） (3/3) - ＠IT より抜粋
内容が同一のクエリで、オプティマイザへのヒントに上記抜粋部分のそれぞれを使用したとき、実行計画や実行時間はどのような変化をするのだろうか。実際にクエリを発行したりして実験をしてみた。
やったこと
データなど準備
CREATE TABLE TBL_10_MAN(CLM1 VARCHAR2(16) NOT NULL); CREATE TABLE TBL_100_MAN(CLM1 VARCHAR2(16) NOT NULL , CONSTRAINT TBL_100_MAN_PK PRIMARY KEY (CLM1));
まずテスト用のテーブルを準備する。それぞれのテーブルは10万件、100万件データをぶっこむ。100万件の方のテーブルだけ主キー制約つけてインデックス貼る。10万件の方はなんもしない。
create or replace PROCEDURE PROC22 AS begin for I in 1..100000 LOOP insert into TBL_10_MAN(CLM1) values((DBMS_RANDOM.STRING('a',16))); end LOOP; commit; END PROC22;
テーブルにランダム文字列のデータを入れていく。そんな主キーで大丈夫か？ って感じですが、あくまで実験なんで良しとしてください。
下記は実際に流したクエリと実行計画。
select * from TBL_10_MAN T1 join TBL_100_MAN T2 on T1.CLM1 = T2.CLM1;
select /*+ FIRST_ROWS */ * from TBL_10_MAN T1 join TBL_100_MAN T2 on T1.CLM1 = T2.CLM1;
各実行計画の違いについて見ていく。
個人的に驚きだったのは、クエリは同一でもヒントが付いてるか付いてないかだけで、これだけ実行計画が変わる、ということ。知識として知ってはいても、アクセス方式とかコストとかこんなに鮮やかな変化を見せるもんなんですなぁ。
ジョインの違い
ジョインがFIRST_ROWSはネステッドループ、ALL_ROWSはHASH_JOINを選択している。ハッシュ結合は、ジョインをする前にハッシュテーブルを作る、要はイニシャルコストがかかる。そうなると、FIRST_ROWSでは、オプティマイザがそのイニシャルコストがかからないネステッドループを選択したのだろう、という推測が立つ。参考：ハッシュ結合(hash join)とは
コストの差
コストの差がやたらあるのが目に付く。ネステッドループは10万、ハッシュ結合は2000とかなってる。ハッシュ結合は安くみられやすかった（ハズ）し、ハッシュテーブルがメモリに乗っちゃえば速い。
とはいえ、このコストは、FIRST_ROWSの有無とは直接は関係が無い。ネステッドループがやたら高コストにされているのは、片方のテーブルがインデックス一つも無いとかいう常識的には有り得ない環境のせいが大きい。TBL_10_MANにインデックスを張ると、コストは6000ほどになる。
これはかなり極端で強引な例ではあるが、いくらFIRST_ROWSを付けてネステッドループに誘導したところで適切なインデックスが無ければ効果が出ない、という可能性を示唆しているように思える。
実行時間とテーブルに入っているデータとの関連
最初の件数が返ってくる時間も、全件の処理時間も、どっちもALL_ROWSの方が速かった。これは、ジョインをしているけれども引っかかるデータが一件も無いデータを入れているから。要は、返せるデータが1件も無いが、それが分かるのは全行をアクセスしたあと、ということ。そのため、ネステッドループでなるべく早く最初の数件を返そうとするも全行アクセスしないことには最初の数件が出てこない（というかこの場合だとゼロ件）のと、ハッシュ結合が事前にハッシュテーブル作ってから全行アクセスするのとでは、ハッシュ結合のほうが早くなってしまう、ということのようだ。
というわけで、同じデータを持つ列のテーブルとジョインしたらどうなるか、をやってみる。100万件入ってる側のテーブルから、10万件をコピーしてテーブルを新しく作る。
insert into TBL_10_MAN_SAME select TBL_100_MAN.CLM1 from TBL_100_MAN where rownum < 100000;
クエリと実行計画。テーブル名を、TBL_10_MAN → TBL_10_MAN_SAMEにしただけで他は何も変えていない。
select * from TBL_10_MAN_SAME T1 join TBL_100_MAN T2 on T1.CLM1 = T2.CLM1;
select /*+ FIRST_ROWS */ * from TBL_10_MAN_SAME T1 join TBL_100_MAN T2 on T1.CLM1 = T2.CLM1;
非常に面白いことに、FIRST_ROWSは一瞬で結果が帰ってくる*2ようになる。クエリを連続で二回流せばバッファキャッシュに乗るからだと思われるが、そうなると0秒*3で結果が返ってくるようになる。データの中身が、最初の数件が即効で見つかるようになったので、レスポンスタイムも大幅に改善された、ということだろう。面白いのは、相変わらずコストの数値は高い*4にも関わらず、レスポンスタイムが激速になった点でしょう。図で書くとこんな感じ。
なお、ALL_ROWSのハッシュ結合の方も、二回以上流せば0.04秒とかにはなるが、決して0秒になることはない。こうなってくると、ハッシュ結合はハッシュテーブルの作成という、どうしても最初に幾ばくかの初期化処理のコストがかかってしまう、という教科書的事実が肌で実感できる。
以上もまた極端で強引な例ではあるが、FIRST_ROWSが効果的かどうかはデータの分布にも影響される、と言える。いくら実行計画が最初の数件を引っ掛けるのに有利なアルゴリズムを選んだとして、最初の数件のデータが中々ヒットしないのでは意味がない。逆に、ヒットするようになれば驚くほどのレスポンスタイムのスピードアップが望める。
火事だっつってバケツリレーで少しずつでもいいから速く水を運ぼうとしたとする。しかし、井戸のすげーー深いとこにしか水が無く、汲み出すのに時間かかるどころか水があるのかすらも分からなかったとしたら、レスポンスタイムは最悪になる。だったら、最初の呼び出し時間はかかるけれどもポンプ車呼んで一気に吸い上げた方が速いかも、てなところでしょうか。もちろん、井戸の水面が高ければバケツのレスポンスタイムは速くなる。
つまり、どんなアクセス方式であれその実行時間はテーブルにどのようなデータが入っているかに影響を受ける。よって、FIRST_ROWSでレスポンスタイムの改善を図ろうとするとき、実行計画だけでなくそのSQLがアクセスするオブジェクトの構造も知る必要がある。もっとくわしいことはデータベースパフォーマンスアップの教科書 基本原理編の部分範囲処理の項を参照してください。
まとめ
ぐぐってると、オンラインではFIRST_ROWS、バッチではALL_ROWSという大まかな目安が書かれていることが多い。Oracleのリファレンスもそんなよーなこと書いてありますし。ただし、闇雲にFIRST_ROWSつけりゃスループットが必ずよくなるってもんじゃないのが今回理解できた。特に、データの中身によって発生する実行時間の差は興味深かった。
昔のRBOとかの時代のOracleはどうだったのか知らないけど、今はもう殆どはデフォルト設定かつオプティマイザ任せでいいんでないの？ という気がする。良く理解せず下手にパラメータをアレコレいじると、余計ややこしくなるといいますか。もちろん、オプティマイザのキモチ（？）を正しく理解した上で色々手を打てばOracleは良いパフォーマンス出すようになるわけで。ミドルウェアというのは、よくわからんくてもそのまま使えば便利、よくわかって使えばもっと便利、という代物な気がするのでした。
