index range scan(索引范围扫描)的计划分析



一、index range scan(索引范围扫描):
1、对于unique index(唯一性索引,是带唯一性约束的,普通索引则没有唯一性约束。scott.emp表是主键,带有唯一性约束,同时有索引Rowid)来说,如果where 条件后面出现了<,> ,between ...and...的时候,那么就可能执行index range scan,如果where条件后面是=,那么就会执行index unique scan。

  例1:where条件后面是=的情况

 

SQL> explain plan for select * from scott.emp a where a.empno='7369';
 
Explained
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |       |       |       |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP      |       |       |       |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."EMPNO"=7369)
Note: rule based optimization
 
15 rows selected

 

    例2:where 条件后面出现了<,> ,between ...and...的情况

SQL> explain plan for select * from scott.emp a where a.empno < '7369';
 
Explained
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |       |       |       |
|*  2 |   INDEX RANGE SCAN          | PK_EMP      |       |       |       |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."EMPNO"<7369)
       filter("A"."EMPNO"<7369)
Note: rule based optimization
 
16 rows selected


2、对于none unique index来说 如果where 条件后面出现了=、>、<、betweed...and...的时候,就有可能执行index range scan。

例子:

 

SQL> create table emp1 as select * from scott.emp;
 
Table created
 
SQL> create index emp1_name_idx on emp1(ename);
 
Index created
 
SQL> explain plan for select * from emp1 a where a.ename = 'SMITH';
 
Explained
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Id  | Operation                   |  Name          | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP1           |       |       |       |
|*  2 |   INDEX RANGE SCAN          | EMP1_NAME_IDX  |       |       |       |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ENAME"='SMITH')
Note: rule based optimization
 
15 rows selected


3、对于组合索引来说,如果where条件后面出现了组合索引的引导列,那么可能执行index range scan。

 

例:

 

SQL> create index emp1_name_empno_idx on emp1(empno,ename);
 
Index created
 
SQL> explain plan for select * from emp1 a where a.ename = 'SMITH' and empno='33';
 
Explained
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                   |  Name                | Rows  | Bytes | Cos
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP1                 |       |       |
|*  2 |   INDEX RANGE SCAN          | EMP1_NAME_EMPNO_IDX  |       |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."EMPNO"=33 AND "A"."ENAME"='SMITH')
Note: rule based optimization
 
15 rows selected

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值