高效的SQL(index skip scan 使用条件)

高效的SQLindex 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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值