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