一、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