INDEX SCANのフィルタ述語を検証する②

テーマ

前の記事から継続。
orcl.hatenablog.com
今回は索引第一列のカーディナリティを下げてみる。

準備

SQL> create table test2(col1 number not null ,col2 number not null);

Table created.

SQL> create index idx_test2 on test2(col1 ,col2);

Index created.

SQL> insert into test2 select mod(level ,10) + 1 ,1000001 - level from dual connect by level <= 1000000;

1000000 rows created.

SQL> select col1 ,count(*) from test2 group by col1 order by 1;

      COL1   COUNT(*)
---------- ----------
         1     100000
         2     100000
         3     100000
         4     100000
         5     100000
         6     100000
         7     100000
         8     100000
         9     100000
        10     100000

10 rows selected.

INSERT文が気持ち分かりにくいが要はCOL1を1～10の範囲に限定しているだけ。

検証1

とりあえず比較用に索引の第一列（COL1）全てを検索する条件。

SQL> select /*+ index(test2 idx_test2) */ count(1) from test2 where col1 >= 1;

  COUNT(1)
----------
   1000000


Execution Plan
----------------------------------------------------------
Plan hash value: 3780423660

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    13 |  2717   (1)| 00:00:33 |
|   1 |  SORT AGGREGATE   |           |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TEST2 |  1004K|    12M|  2717   (1)| 00:00:33 |
-------------------------------------------------------------------------------

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
       2652  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

読み取りブロック数は2652。

検証2

索引第二列（COL2）を一意に絞る条件を追加。

SQL> select /*+ index(test2 idx_test2) */ count(1) from test2 where col1 >= 1 and col2 = 1;

  COUNT(1)
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 3780423660

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    26 |  2717   (1)| 00:00:33 |
|   1 |  SORT AGGREGATE   |           |     1 |    26 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TEST2 |    21 |   546 |  2717   (1)| 00:00:33 |
-------------------------------------------------------------------------------

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
       2652  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になって読み取りブロック数は検証1のときと同じ。
やはり前回の記事同様に「COL2=1」はfilterしかしてないように見える。
accessとfilter両方に出ている場合はもう実質filterだと思えば良いのか？

検証3

ヒント句でSKIP SCANを指定。

SQL> select /*+ index_ss(test2 idx_test2) */ count(1) from test2 where col1 >= 1 and col2 = 1;

  COUNT(1)
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2609643709

------------------------------------------------------------------------------
| 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_TEST2 |    21 |   546 |    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
         17  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のときと変わらないが読み取りブロックが劇的に減った。
索引の前の方の列のカーディナリティが低いとある程度効率的な読み取りが出来る様子。
なんとなくSKIP SCANの動きも分かったような気がする。

まとめ

accessとfilterで重複して述語が出ている場合は

• RANGE SCANの場合は実質filterしかしていないと思って良さそう（多分）
• SKIP SCANの場合はカーディナリティによってはaccessできそう（表現怪しい）

そもそもCHAR(2)くらいの種別コードやフラグ列なんかの索引でもないとSKIP SCAN自体採用されないのかもしれない。
↓こういう記事もあるし。
www.shift-the-oracle.com