INDEX SCANのフィルタ述語を検証する①
一発目の記事から早速地味な検証。
テーマ
INDEX SCANした時にaccessとfilterが重複してることが稀に良くある。
↓こういうの。
Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1"=1 AND "COL3"=1000) filter("COL3"=1000)
accessなのかfilterなのか良く分からないので検証してみる。
そもそも再現パターンがよく分かっていないが何となく索引の第一列目の絞込み効率が良くないときに出そうな気がする。
autotraceで読み取りブロック数を比較するのが手っ取り早いかなと思う。
accessとかfilterについてはこの辺が参考になるかも。
http://use-the-index-luke.com/ja/sql/explain-plan/oracle/filter-predicates
http://www.csus4.net/d/2011/06/30/predicate/
準備
とりあえず検証用テーブル作成。
読み取りブロック数がある程度多い方が分かりやすいのでいっぱい入れる。
SQL> create table test1(col1 number not null ,col2 number not null); Table created. SQL> create index idx_test1 on test1(col1 ,col2); Index created. SQL> insert into test1 select level ,1000001 - level from dual connect by level <= 1000000; 1000000 rows created. SQL> select * from (select * from test1 order by 1) where rownum <= 10; COL1 COL2 ---------- ---------- 1 1000000 2 999999 3 999998 4 999997 5 999996 6 999995 7 999994 8 999993 9 999992 10 999991 10 rows selected.
COL1、COL2はそれぞれ適当にユニークな数値を入れる。
検証1
とりあえず索引の第一列（COL1）が全て当てはまる条件で検索する。
SQL> select /*+ index(test1 idx_test1) */ count(1) from test1 where col1 >= 1; COUNT(1) ---------- 1000000 Execution Plan ---------------------------------------------------------- Plan hash value: 3196887443 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2993 (1)| 00:00:36 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| IDX_TEST1 | 1164K| 14M| 2993 (1)| 00:00:36 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2924 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
これで索引全体を読み取っているはず。
このときconsistent getsの値を見るに2924ブロック。
検証2
今度は索引の第二列（COL2）で一意に絞れる条件を足してみる。
これで例の実行計画が再現できる気がする。
SQL> select /*+ index(test1 idx_test1) */ count(1) from test1 where col1 >= 1 and col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 3196887443 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2993 (1)| 00:00:36 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | INDEX RANGE SCAN| IDX_TEST1 | 23 | 598 | 2993 (1)| 00:00:36 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1 AND "COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2924 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
思惑通りに「COL2=1」がaccessとfilter両方に出てきた。
検証1のときと同じ読み取り量なので実質filterしかしてないように見える。
少なくともaccessに出ているからといって楽観視して良いものでもないということは言えそう。
検証3
今度はヒント句を変えてSKIP SCANにしてみる。
なんとなくSKIP SCANならいける気がする。
SQL> select /*+ index_ss(test1 idx_test1) */ count(1) from test1 where col1 >= 1 and col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2617422197 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 26 | 44 (14)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | INDEX SKIP SCAN| IDX_TEST1 | 23 | 598 | 44 (14)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1 AND "COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2928 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
むしろ若干増えた。
これは一応RANGE SCANとは違う動きをしている気がする。
検証4
ついでに索引の第一列を条件に含まないパターンも試してみる。
SQL> select /*+ index(test1 idx_test1) */ count(1) from test1 where col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2208524269 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | 2995 (1)| 00:00:36 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX FULL SCAN| IDX_TEST1 | 23 | 299 | 2995 (1)| 00:00:36 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2924 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
FULL SCANなので検証1とほぼ同じ動きで予想通り。
accessも糞もない気がするがもうお作法的なものなのかもしれない。
検証5
今度は検証4と同じ条件でSKIP SCANを試してみる。
SQL> select /*+ index_ss(test1 idx_test1) */ count(1) from test1 where col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2617422197 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | 4003 (1)| 00:00:49 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX SKIP SCAN| IDX_TEST1 | 23 | 299 | 4003 (1)| 00:00:49 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2928 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
検証3と同じ読み取りブロック数に見える。
というかFULL SCANと変わらなそうなのでSKIP SCANの存在意義が怪しい。
若干本題とはずれるが、もしかして索引の第一列のカーディナリティが高すぎるのでうまく第二列から索引の構造を使えてないのかも？（勘）
検証6
せっかくなのでFAST FULL SCANの試してみる。
SQL> select /*+ index_ffs(test1 idx_test1) */ count(1) from test1 where col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2671621383 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 816 (1)| 00:00:10 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX FAST FULL SCAN| IDX_TEST1 | 23 | 299 | 816 (1)| 00:00:10 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2939 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
完全にfilterだけになって分かりやすくなった。
FAST FULL SCANは索引のツリー構造を無視してリーフを全部読み取るはず。
accessの余地がないのは明らかか。
まとめ
微妙。
やはり索引第一列のカーディナリティが高すぎて変化が出ていない部分があるかもしれない。
ただしaccessに出ていても実質filterしか出来てないということはありそう。
