損益分岐点というたとえが正確かどうかはわからないが。範囲検索がある一定以上になると、インデックスよりもフルスキャンのほうが速くなるポイントがある。というわけで、それはどのへんにあるのかをやってみる。
やることとしては、連番の数値と文字列の2列からなるテーブルを用意し、数値列に対する範囲検索を実行して、インデックス範囲スキャンとフルテーブルスキャンの実行時間を計測する。数値列には一意インデックスを張る。
環境
- Oracle Database Express Edition 11g Release 2をWindows上で動作
- Oracle SQL Developer 3.2
- SQL*Plus: Release 11.2.0.2.0 Production
準備
検索対象となる適当なテーブルを作る。
create table dest (search integer, rand_str varchar2(10));
100万件レコードを入れる。数値列は1からの昇順で連番、もいっこはランダム文字列10桁。
insert into dest(search, rand_str) select rownum, DBMS_RANDOM.STRING('X', 10) from (select 0 from all_catalog where rownum <= 1000), (select 0 from all_catalog where rownum <= 1000); commit;
一意インデックスをつくる。
create unique index ind_dest_search on dest (search);
計測用のクエリ。ヒントでインデックス範囲スキャンに誘導する。クエリ実行前にバッファキャッシュをクリアして、影響を除く。<= ?の数値を変えて計測する。SQL*plusでset timing onして、検索結果がすべて戻るまでの実行時間を計測する。
set timing on alter system flush buffer_cache; select /*+ INDEX(DEST IND_DEST_SEARCH) */ search, rand_str from dest where search <= 100;
計測用のクエリその２。ヒントでテーブルフルスキャンに誘導する。
set timing on alter system flush buffer_cache; select /*+ FULL(DEST) */ search, rand_str from dest where search <= 100;
実行結果
| INDEX | FULL | |||||||
|---|---|---|---|---|---|---|---|---|
| 行数 | 割合（％） | 1 | 2 | 3 | 1 | 2 | 3 | |
| 1 | 0.0001 | 0.03 | 0.07 | 0.03 | 1.35 | 1.01 | 0.84 | |
| 10 | 0.001 | 0.03 | 0.09 | 0.03 | 1.29 | 1.03 | 0.87 | |
| 100 | 0.01 | 0.07 | 0.09 | 0.10 | 1.45 | 1.06 | 0.89 | |
| 1000 | 0.1 | 0.14 | 0.15 | 0.14 | 1.10 | 0.96 | 0.98 | |
| 5000 | 0.5 | 0.59 | 0.54 | 0.54 | 1.29 | 1.28 | 1.31 | |
| 10000 | 1 | 1.12 | 1.06 | 1.04 | 1.95 | 1.76 | 01.84 | |
| 50000 | 5 | 5.29 | 4.81 | 4.78 | 5.57 | 5.39 | 5.14 | |
| 100000 | 10 | 10.14 | 10.87 | 10.25 | 10.62 | 09.46 | 09.56 | |
| 200000 | 20 | 20.34 | 20.34 | 20.39 | 18.64 | 18.17 | 18.14 | |
| 300000 | 30 | 30.00 | 29.82 | 30.01 | 25.54 | 25.65 | 26.71 | |
| 400000 | 40 | 41.12 | 40.21 | 39.81 | 35.70 | 33.81 | 33.70 | |
| 500000 | 50 | 49.82 | 49.78 | 49.84 | 43.45 | 43.95 | 41.14 | |
| 600000 | 60 | 1:03.29 | 1:00.96 | 1:00.86 | 51.79 | 49.23 | 53.20 | |
| 700000 | 70 | 1:12.40 | 1:13.48 | 1:12.06 | 1:01.65 | 58.82 | 58.32 | |
| 800000 | 80 | 1:22.45 | 1:21.90 | 1:22.01 | 1:01.91 | 1:09.96 | 1:07.28 | |
| 900000 | 90 | 1:34.65 | 1:37.56 | 1:28.00 | 1:19.10 | 1:15.23 | 1:17.54 | |
| 1000000 | 100 | 1:42.92 | 1:56.01 | 1:45.68 | 1:28.75 | 1:27.10 | 1:23.53 | |
というわけで、このケースでは損益分岐点は10%のあたりのようです。
感想とか
まず、10%という値は環境依存であると予想できる。実行結果のフルスキャンの結果を見ると、1%以下の実行時間はほぼ変化しない。なので、この時間が正味の全ブロック読み出すのにかかる時間と推測される。んでまぁ、このテストテーブルは1秒かそこらで読み終わってしまう程度には小さい。常識的に考えれば、これはかなり安いので、このケースではフルスキャンの方が比較的優位になる。なので、一般的な損益分岐点は、10%以上になるのではなかろーか、と推測が立てられる。
ちょっと話が逸れるが、Oracleのパフォーマンス関連の本を読んでいると、あるパフォーマンス改善の手段が役に立つかどうかは、実際に具体的な（本番）環境で計測してみること以上に正確な成果を計る手段は無い、みたいなことがしばしば見られる。今回のエントリで色々試してて実感したんだが、OracleちゅうかRDBMSはどのような使い方をしているか、にかなり依存する。マッタク同一のテーブル構成であっても、入ってるデータ分布や量によっては同一のクエリでも同じ実行時間にはならない。マシンなど物理環境は言うに及ばず。
いや何が言いたいかっていうと、この10%という値はあくまでもこの環境でのものでしかないよね、ってことなんですが。
それはそれとして。10%以下の領域だと、フルスキャンは1秒あたりで横ばいになる。対してインデックススキャンは極限まで小さくなっていく。やはり、テーブルのごく少数のデータを取り出す場合だと、インデックスは圧倒的に早い。
ところで、フルスキャンは1件だろうが全件だろうが全ブロック読み込むことに変わりは無い。範囲検索条件によってやることが変わらないとなると、実行時間がこれほど変化するのは何故なのか。1%以下の領域ではフルスキャンの実行時間が横ばいになるのは何なのか。これは、ブロックを読み込んだあとクライアント（この場合SQL*Plus）に返した行数の差によるところが大きいかと。実行時間を考える上では、読み込み対象となるブロック数はもとより、最終的に返される行数もまた重要な要素なんだと思う。SQL*Plusのフェッチ関連のパラメータはなんも変えてないので、その辺変えると実行時間はまた変わりそうである。
10%を越える領域だと、少しずつフルスキャンが有利になっていく。やはり行数が増えていくと、次第にインデックス経由のオーバーヘッドが重しになっていくのが分かる。また、インデックスとテーブルの行順序は一致しているので、インデックスからテーブルへの（ランダム）アクセスのコストは最小限度で済んでいる。これがバラバラの場合、範囲スキャンはより不利になるので、CLUSTERING_FACTORが低い場合はそんだけ損益分岐点は下がりそうである。参考：表の行順序とインデックス範囲スキャン - kagamihogeのblog
というわけで、確実に言えることは、範囲検索で常にインデックス範囲スキャンが有利かというとそうでもないこと。また、損益分岐点がどっかにあること。といったところでしょうか。
