Index Only Access (Index Only Scan) っていいよね（デメリットもあるけどさ） #1 Tweet
さて、OracleさんがAppleさんのOSで楽しいことしてくれないから最近つまんなくなりつつあるので、普通にSQL文のチューニングネタです。
鬼熱かった、鬼熱かった！ :: Insight out 2011- DB tech showcaseでもかなり触れていた、Index Only Scan日本ではこの表現が割合的に多いようなのですが、Index Only Accessって言ってましたね、トムカイトさんも。英語では後者のほうが一般的なのかもしれません。ここではIndex Only Accessってことにしておきます。;)
デメリットもあるけど、Index Only Accessのいいとこばかりを中心に、書いちゃうよ〜w
まず、前提から。
TAB10表は以下のように定義してあります。意図的に表データが大きくなるようにしてあります :)
名前 NULL? 型
------------------ -------- --------------
SEQ# NOT NULL NUMBER
NON_UNIQUE_KEY NOT NULL CHAR(10)
DATA VARCHAR2(500)
また、以下のような索引を事前に索引しているが、PK_TAB10という主キー索引以外はINVISIBLEとして作成してある。
INVISIBLEで作成しておくと、索引は通常通りメンテナンスされるが、オプティマイザは実行計画作成時にINVISIBLEな索引を利用しないというOracle11gから登場した便利な機能
また前述の表には以下のような主キー(PK_TAB10)と非ユニークな索引が２つ作成してあります。（あまり良い例ではないですがご勘弁を）
但し、TAB10_I01、TAB10_I02の２索引は、INVISIBLEで作成してあります。効果確認時など便利ですよね。
(不可視索引の詳細はマニュアル「Oracle Database 管理者ガイド 11gリリース1（11.1）不可視索引の作成」を参照のこと。）
INDEX_NAME COLUMN_NAME
------------ -------------------
PK_TAB10 SEQ#
TAB10_I01 NON_UNIQUE_KEY
TAB10_I02 NON_UNIQUE_KEY
SEQ#
検証時の処理時間及び、実行統計は、各クエリを２回実行し２回目の処理時間及び、実行統計を載せてあります。
（２回目の実行前にバッファキャッシュをクリアしてあるので、ソフトパース＋キャッシュミスほぼ100%という状況の処理時間及び実行統計情報です。）
テストデータは以下件数で、non_unique_keyは偏りはなく均一に分布させあります。
実際のはなし、均等になることの方が稀ではあると思いますけど、Index Only Accessの効果を見る事ができればそれでOKなので。
COUNT(1)
----------
800000
COUNT(DISTINCTNON_UNIQUE_KEY)
-----------------------------
80000
NON_UNIQUE COUNT(1)
---------- ----------
0000000001 10
0000000002 10
0000000003 10
0000000004 10
0000000005 10
0000000006 10
0000000007 10
0000000008 10
0000000009 10
0000000010 10
0000000011 10
0000000012 10
0000000013 10
0000000014 10
0000000015 10
0000000016 10
0000000017 10
0000000018 10
0000000019 10
0000000020 10
・・・以下略・・・
まず最初は一番分かりやすい、Index Only Accessの例から。
non_unique_key列に、TAB10_I01という非ユニーク索引を作成してありますが、現状、INVISIBLE状態にしてあるため索引が無く全表走査がとなっていますよね。
SQL> set autot trace exp stat
SQL>
SQL> select count(non_unique_key) from tab10 where non_unique_key between '0000000001' and '0000000010';
経過: 00:00:04.32
実行計画
----------------------------------------------------------
Plan hash value: 1584905094
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 16787 (1)| 00:03:22 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| TAB10 | 100 | 1100 | 16787 (1)| 00:03:22 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NON_UNIQUE_KEY"<='0000000010' AND
"NON_UNIQUE_KEY">='0000000001')
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
61544 consistent gets
61539 physical reads
0 redo size
562 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ここで、TAB10_I01索引をVISIBLEへ変更して同一クエリを実行してみると……
TAB10_I01索引を利用した実行計画はIndex Range Scanに変化します。しかも、表データはアクセスしていません。これがIndex Only Accessの典型的な例です。アクセスブロック数も処理時間も大きく改善していますよね。
ただ、Index only scanにはデメリットもあります。
それは、索引が多くなればなるほどDML文には足枷になり遅くなるため点です。参照と更新、挿入、削除のバランスを取るのが大切ですが、
とにかく参照を速くする、更新、挿入、削除の処理性能などは少々犠牲にしても問題ないのであれば、どんどんやっちゃいます！（ご利用は計画的にw)
SQL> alter index tab10_i01 visible;
SQL>
SQL> select count(non_unique_key) from tab10 where non_unique_key between '0000000001' and '0000000010';
経過: 00:00:00.02
実行計画
----------------------------------------------------------
Plan hash value: 3770330610
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | INDEX RANGE SCAN| TAB10_I01 | 100 | 1100 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NON_UNIQUE_KEY">='0000000001' AND
"NON_UNIQUE_KEY"<='0000000010')
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
3 physical reads
0 redo size
562 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ちなみに、クエリは少々違うのですが、Index only accessで無い場合は、index range scan＋table access by index rowidとなり以下のような実行計画になっちゃいます。
（こちらの方がよく目にする実行計画じゃないでしょうかね。私も性能的な問題等なければ以下のような実行計画であれば良しとしておくことが多いのも事実です。。）
実行計画
----------------------------------------------------------
Plan hash value: 1097902029
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 51700 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TAB10 | 100 | 51700 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB10_I02 | 100 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
次回へつづく。（忙しくてなかなか書けないかもw)
| 固定リンク | 0
コメント