Index Skip Scans的产生
11G官方解释：
Index Skip Scans
Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the
query. In other words, it is skipped.
The database determines the number of logical subindexes by the number of distinct values in the initial column. Skip scanning is advantageous when there are few
distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.
The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate. For example, assume that you run the
following query for a customer in the sh.customers table:
SELECT * FROM sh.customers WHERE cust_email = 'Abbey@company.com';
The customers table has a column cust_gender whose values are either M or F. Assume that a composite index exists on the columns (cust_gender, cust_email) that was
created as follows:
CREATE INDEX customers_gender_email ON sh.customers (cust_gender, cust_email);
shows a portion of the index entries.
Example 11-6 Composite Index Entries
F,Wolf@company.com,rowid
F,Wolsey@company.com,rowid
F,Wood@company.com,rowid
F,Woodman@company.com,rowid
F,Yang@company.com,rowid
F,Zimmerman@company.com,rowid
M,Abbassi@company.com,rowid
M,Abbey@company.com,rowid
The database can use a skip scan of this index even though cust_gender is not specified in the WHERE clause.
In a skip scan, the number of logical subindexes is determined by the number of distinct values in the leading column. In , the leading column has two possible values.
The database logically splits the index into one subindex with the key F and a second subindex with the key M.
When searching for the record for the customer whose email is Abbey@company.com, the database searches the subindex with the value F first and then searches the
subindex with the value M. Conceptually, the database processes the query as follows:
SELECT * FROM sh.customers WHERE cust_gender = 'F'
AND cust_email = 'Abbey@company.com'
UNION ALL
SELECT * FROM sh.customers WHERE cust_gender = 'M'
AND cust_email = 'Abbey@company.com';
测试如下：
RACDB1>CONN XHM/XHM
Connected.
RACDB1>CREATE TABLE TEST1 AS SELECT * FROM DBA_OBJECTS;
Table created.
创建前缀列重复值高的复合索引，可以使用INDEX SKIP SCAN（注意需要收集下统计信息）
RACDB1>create index test1_idx on test1(status,owner,object_type,object_id);
Index created.
RACDB1>select * from test1 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1062 | 130 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST1 | 6 | 1062 | 130 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
569 consistent gets
0 physical reads
0 redo size
1400 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
收集统计信息：
RACDB1>exec dbms_stats.gather_table_stats('XHM','TEST1');
PL/SQL procedure successfully completed.
RACDB1>select * from test1 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3492674483
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 29 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 93 | 29 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST1_IDX | 1 | | 28 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
filter("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
569 consistent gets
0 physical reads
0 redo size
1400 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
RACDB1>select * from test1 where wner='XHM'and object_id='2';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3492674483
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 30 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 93 | 30 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST1_IDX | 1 | | 29 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='XHM' AND "OBJECT_ID"=2)
filter("OWNER"='XHM' AND "OBJECT_ID"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
RACDB1>select * from test1 where wner='XHM'and object_type='TABLE' AND object_id='2';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3492674483
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 93 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST1_IDX | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='XHM' AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=2)
filter("OWNER"='XHM' AND "OBJECT_ID"=2 AND "OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
RACDB1>select * from test1 where status='VALIDED' and object_type='TABLE' AND object_id='2';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3492674483
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 93 | 9 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST1_IDX | 1 | | 8 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='VALIDED' AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=2)
filter("OBJECT_ID"=2 AND "OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
RACDB1>select * from test1 where status='VALIDED' and wner='XHM';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1885706448
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 93 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='VALIDED' AND "OWNER"='XHM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
RACDB1>select * from test1 where status='VALIDED' and wner='XHM' and object_id=2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1885706448
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 93 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='VALIDED' AND "OWNER"='XHM' AND "OBJECT_ID"=2)
filter("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
RACDB1>select * from test1 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3492674483
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 33 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 93 | 33 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST1_IDX | 1 | | 32 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
filter("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
569 consistent gets
0 physical reads
0 redo size
1400 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
相对的，创建前缀列重复值低的复合索引，无法使用INDEX SKIP SCAN：
RACDB1>CREATE TABLE TEST2 AS SELECT * FROM DBA_OBJECTS;
Table created.
RACDB1>create index test2_idx on test2(object_id,object_type,owner,status);
Index created.
RACDB1>select * from test2 where wner='XHM';
Execution Plan
----------------------------------------------------------
Plan hash value: 300966803
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1062 | 130 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST2 | 6 | 1062 | 130 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='XHM')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
632 consistent gets
0 physical reads
0 redo size
1574 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
同样收集统计信息：
RACDB1>exec dbms_stats.gather_table_stats('XHM','TEST2');
PL/SQL procedure successfully completed.
RACDB1>select * from test2 where wner='XHM';
Execution Plan
----------------------------------------------------------
Plan hash value: 300966803
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 130 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST2 | 1 | 93 | 130 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='XHM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
569 consistent gets
0 physical reads
0 redo size
1574 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
RACDB1>exec dbms_stats.gather_table_stats('XHM','TEST2');
PL/SQL procedure successfully completed.
RACDB1>select * from test2 where object_type='table' and wner='XHM';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 300966803
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 130 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST2 | 1 | 93 | 130 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='table' AND "OWNER"='XHM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26655292/viewspace-752030/ ，如需转载，请注明出处，否则将追究法律责任。
