index skip scan

最新推荐文章于 2024-09-28 21:43:12 发布

blockheading

最新推荐文章于 2024-09-28 21:43:12 发布

阅读量326

点赞数

分类专栏： Oracle Basic Principle 文章标签：索引 oracle

本文链接：https://blog.csdn.net/u013169075/article/details/53000832

版权

Oracle Basic Principle 专栏收录该内容

57 篇文章 0 订阅

订阅专栏

--index skip scan

--An index scan occurs in which the initial column of a composite index is "skipped" or not specified in the query. For example, if the composite index key is (cust_gender,cust_email), then the query predicate does not reference the cust_gender column.
--Skip scan会探测出索引前导列的唯一值个数，每个唯一值都会作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询

EODA@PROD1> create table t
  2    as
  3    select decode(mod(rownum,2), 0, 'M', 'F' ) gender, all_objects.*
  4    from all_objects
  5  /

Table created.

EODA@PROD1> 
EODA@PROD1> create index t_idx on t(gender,object_id);

Index created.

EODA@PROD1> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

EODA@PROD1> 
EODA@PROD1> set autotrace traceonly explain
EODA@PROD1> select * from t t1 where object_id = 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 2053318169

-------------------------------------------------------------------------------------
| Id  | Operation		    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	    |	  1 |	 99 |	  4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |	  1 |	 99 |	  4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN	    | T_IDX |	  1 |	    |	  3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=42)
       filter("OBJECT_ID"=42)
	   
EODA@PROD1> select * from t t1 where object_id = 42;  --删除索引后查看全表扫描的执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    99 |   297   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T	 |     1 |    99 |   297   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=42)