高效的SQL（index skip scan 使用条件）

高效的SQL（index skip scan使用条件）

 

Index Skip Scan

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 number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

 

INDEX SKIP SCAN 使用条件

1、  index skip scan often are used in composite index

2、  leading column (few distinct) and nonleading column (many distinct)

3、  initial column not specified in the query and other words specified in the query

 

1)       leading column(few distinct) are M and F

doudou@TEST> create table tab_skip as select decode(mod(rownum,2),0,'M','F') gender , all_objects.* from all_objects;

 

Table created.

 

doudou@TEST> create index tab_skip on tab_skip(gender,object_id);

 

Index created.

 

doudou@TEST> exec dbms_stats.gather_table_stats('DOUDOU','TAB_SKIP',cascade=>true);

 

PL/SQL procedure successfully completed.

 

doudou@TEST> set autot trace

 

2)      initial column(gender) not specified in the query and other words(object_id) specified in the query

doudou@TEST> select * from tab_skip where object_id=258;

 

Execution Plan

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

Plan hash value: 123902562

 

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |          |     1 |    97 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB_SKIP |     1 |    97 |     4   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | TAB_SKIP |     1 |       |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("OBJECT_ID"=258)

       filter("OBJECT_ID"=258)

 

Statistics

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

          0  recursive calls

          0  db block gets

          7  consistent gets

          0  physical reads

          0  redo size

       1256  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

【There are “INDEX SKIP SCAN”】

 

3)        leading column(many distinct) are 1..256

doudou@TEST>  create table tab_skip01 as select chr(mod(rownum,256)) gender,all_objects.* from all_objects;

 

Table created.

 

doudou@TEST> create index tab_skip01 on tab_skip01(gender,object_id);

 

Index created.

 

doudou@TEST> exec dbms_stats.gather_table_stats('DOUDOU','TAB_SKIP01',cascade=>true);

 

PL/SQL procedure successfully completed.

 

4)       initial column(gender) not specified in the query and other words(object_id) specified in the query

doudou@TEST> select * from tab_skip01 where object_id=258;

 

Execution Plan

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

Plan hash value: 258644213

 

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

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |            |     1 |    97 |   132   (0)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| TAB_SKIP01 |     1 |    97 |   132   (0)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID"=258)

 

 

Statistics

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

          1  recursive calls

          0  db block gets

        583  consistent gets

          0  physical reads

          0  redo size

       1256  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 【There are not “INDEX SKIP SCAN”.Bacause leading column values are (many distinct)】

 

 

总结：

         INDEX SKIP SCAN使用条件：

1、  组合索引（composite indexes）

2、  领导列选择度低（leading column few distinct），其他列选择度高（nonleading column many distinct）

3、  查询where语句中没有指明领导列（not specified leading column in the query），但指明了其他列（specified nonleading column）