index range scan 和index skip scan

本文详细解析了Oracle数据库中不同类型的索引扫描方法,包括INDEX Range Scan和Index Skip Scan,并通过实际案例展示了如何选择合适的索引策略以提高查询效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


准备:

create index  ind_id_ot on id(object_type,namespace) tablespace  tsind01


index range scan是一种很常见的表访问方式

          在INDEX Range SCAN中,Oracle访问毗邻的索引条目,然后根据索引里面的rowid去检索表的记录

SQL> set autotrace traceonly
SQL> set linesize 200

SQL> select * from id where object_type='TABLE';



2881 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 2823458674


-----------------------------------------------------------------------------------------
| Id  | Operation    | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    | |  2881 |   208K|   125   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ID |  2881 |   208K|   125   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN    | IND_ID_OT |  2881 | |     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


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


   2 - access("OBJECT_TYPE"='TABLE')




Statistics
----------------------------------------------------------
 0  recursive calls
 0  db block gets
584  consistent gets
 0  physical reads
 0  redo size
     320300  bytes sent via SQL*Net to client
       2632  bytes received via SQL*Net from client
194  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
       2881  rows processed


SQL> 
SQL> select * from id where object_type='TABLE' and namespace=2;


no rows selected




Execution Plan
----------------------------------------------------------
Plan hash value: 2823458674


-----------------------------------------------------------------------------------------
| Id  | Operation    | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    | |    25 |  1850 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ID |    25 |  1850 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | IND_ID_OT |    25 | |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


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


   2 - access("OBJECT_TYPE"='TABLE' AND "NAMESPACE"=2)




Statistics
----------------------------------------------------------
 1  recursive calls
 0  db block gets
 3  consistent gets
 0  physical reads
 0  redo size
       1343  bytes sent via SQL*Net to client
509  bytes received via SQL*Net from client
 1  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
 0  rows processed


index  skip scan  利用了前导列没有在where 条件的索引。即充分利用了现有索引又适当加快了数据查询速度。

 ORACLE官方说,在前导列唯一值较少的情况下,才会用到index skip can。这个其实好理解,就是入口要少,这也是skip scan的条件

SQL> select * from id where  namespace=2;
1516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3621836617


-----------------------------------------------------------------------------------------
| Id  | Operation    | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    | |  1516 |   109K|   492   (0)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ID |  1516 |   109K|   492   (0)| 00:00:06 |
|*  2 |   INDEX SKIP SCAN    | IND_ID_OT |  1516 | |   456   (0)| 00:00:06 |
-----------------------------------------------------------------------------------------


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


   2 - access("NAMESPACE"=2)
       filter("NAMESPACE"=2)




Statistics
----------------------------------------------------------
 1  recursive calls
 0  db block gets
573  consistent gets
 0  physical reads
 0  redo size
     168541  bytes sent via SQL*Net to client
       1631  bytes received via SQL*Net from client
103  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)

       1516  rows processed

SQL> select * from id where object_type='TABLE' and namespace=1;


2881 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 2823458674


-----------------------------------------------------------------------------------------
| Id  | Operation    | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    | |  2720 |   196K|    75   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ID |  2720 |   196K|    75   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | IND_ID_OT |  2720 | |    10   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


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


   2 - access("OBJECT_TYPE"='TABLE' AND "NAMESPACE"=1)




Statistics
----------------------------------------------------------
 1  recursive calls
 0  db block gets
587  consistent gets
 0  physical reads
 0  redo size
     320300  bytes sent via SQL*Net to client
       2632  bytes received via SQL*Net from client
194  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
       2881  rows processed

为了验证index  skip  scan 的使用条件,我们重新建立索引如下

create index  ind_id_ot on id(object_id,namespace) tablespace  tsind01

SQL> select * from id where  namespace=2;


1516 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 377127794


--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |  1516 |   109K|   522   (1)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| ID |  1516 |   109K|   522   (1)| 00:00:07 |
--------------------------------------------------------------------------


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


   1 - filter("NAMESPACE"=2)




Statistics
----------------------------------------------------------
 1  recursive calls
 0  db block gets
       1944  consistent gets
 0  physical reads
 0  redo size
      75174  bytes sent via SQL*Net to client
       1631  bytes received via SQL*Net from client
103  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
       1516  rows processed

如上object_id为唯一值列,会有很多值。虽然有相应的索引包含了namespace 列也没有使用到该索引

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值