轉載

官方文件：

Anindex skip scanuses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index.

理解：當符合索引的前導列重複之比較多的時候，即使在where字句中沒有出現前導列，oracle也會時候符合索引，使用方法是index skip scan。

例子：

建立測試表
SQL> create table test2 as select * from all_objects;

表已建立。

SQL> create index idx_test2 on test2(object_type,object_id);

索引已建立。

SQL> set autot traceonly explain
SQL> select object_name from test2 where object_id-20;
為執行表分析前，查詢不使用索引
SQL> select object_name from test2 where object_id=20;

執行計劃
----------------------------------------------------------
Plan hash value: 300966803

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |    11 |   330 |   277   (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST2 |    11 |   330 |   277   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=20)

Note
-----
   - dynamic sampling used for this statement (level=2)
進行表分析
SQL> analyze table test2 compute statistics;

表已分析。
雖然where字句中沒有用到idx_test2的前導列，但是oracle自動分析以index skip scan的方式使用idx_test2
SQL> select object_name from test2 where object_id=20;

執行計劃
----------------------------------------------------------
Plan hash value: 3100316192

--------------------------------------------------------------------------------
---------

| Id | Operation                   | Name      | Rows | Bytes | Cost (%CPU)| T
ime     |

--------------------------------------------------------------------------------
---------

|   0 | SELECT STATEMENT            |           |     1 |    28 |    42   (0)| 0
0:00:01 |

|   1 | TABLE ACCESS BY INDEX ROWID| TEST2     |     1 |    28 |    42   (0)| 0
0:00:01 |

|* 2 |   INDEX SKIP SCAN           | IDX_TEST2 |     1 |       |    41   (0)| 0
0:00:01 |

--------------------------------------------------------------------------------
---------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=20)
       filter("OBJECT_ID"=20)
where字句中包含前導列，則以index range scan的方式使用idx_test2
SQL> select object_name from test2 where object_type='TABLE' and object_name='TEST2';

執行計劃
----------------------------------------------------------
Plan hash value: 4047680367

--------------------------------------------------------------------------------
---------

| Id | Operation                   | Name      | Rows | Bytes | Cost (%CPU)| T
ime     |

--------------------------------------------------------------------------------
---------

|   0 | SELECT STATEMENT            |           |     1 |    32 |    79   (0)| 0
0:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| TEST2     |     1 |    32 |    79   (0)| 0
0:00:01 |

|* 2 |   INDEX RANGE SCAN          | IDX_TEST2 | 1776 |       |     8   (0)| 0
0:00:01 |

--------------------------------------------------------------------------------
---------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='TEST2')
   2 - access("OBJECT_TYPE"='TABLE')

SQL> spool off;

總結：當複合索引的前導列重複值很大的時候，如果在where字句中出現複合索引的非前導列並且沒有使用前導列，則oracle會自動判斷以index skip scan的方式使用複合索引。（注意：一般符合索引的前導列是重複值比較少的列，如果出現使用index skip scan，請分析複合索引建立的是否合理）。

理解index skip scan

相關文章