索引范围扫描
当数据库需要访问具有高度选择性的数据时，它执行索引范围扫描。数据库按升序返回索引列的值。如果所有行的索引列都是相同的，则按rowid的顺序输出。
当处理以下类型的条件时，优化器会选择索引范围扫描，其中col1是某个索引的前导列:
col1=:b1 col1<:b1 col1>:b1
此外，包含索引前导列的上述三种条件的任何”与 (and)组合，也将导致索引范围扫描。
索引数据以升序存储。如果数据库需要返回降序的数据时，比如需要首先传回最新的数据，或需要检索小于某一具体的数据时，它使用降序索引范围扫描。
SQL> select * from emp where department_id<20;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400 101 10
Execution Plan
----------------------------------------------------------
Plan hash value: 1909286381
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 759 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 11 | 759 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_IDX | 11 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"<20)
从底部开始阅读，解释计划表明，优化器首先执行索引范围扫描，并使用从索引范围扫描获得的rowid执行表访问(table access by index rowid batched操作)。在指定了“大于”条件，如”department_id>100″时，数据库也可能使用索引范围扫描，如下面的例子所示:
SQL> select * from emp where department_id>100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300 205 110
Execution Plan
----------------------------------------------------------
Plan hash value: 1909286381
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 759 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 11 | 759 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_IDX | 11 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID">100)
当查询使用between操作符时，数据库也会执行索引范围扫描，如下面的例子所示:
SQL> select * from emp where department_id between 100 and 110;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000 108 100
110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800 108 100
113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900 108 100
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300 205 110
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1909286381
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1380 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 20 | 1380 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_IDX | 20 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID">=100 AND "DEPARTMENT_ID"< =110)
请注意，虽然在指定条件"where department_id <20"及"where department_id>100"时，数据库使用索引范围扫描，但如果把查询谓词修改为"where department_id<1200",它就会转而执行全表扫描。原因很简单:在指定条件为"department_id<20"或"department_id>9000"的条件时，相比条件"department_id<1200"，数据库需要扫描的值要少得多。很可能有大量的值都满足条件"department_id<120"，因此在指定这个条件时，数据库将执行全表扫描来检索数据，如下面的示例所示:
SQL> select * from emp where department_id<1200;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1107 | 7383 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 1107 | 7383 | 13 (0)| 00:00:01 |
--------------------------------------------------------------------------
2 - access("DEPARTMENT_ID"<1200)
以下是另一个例子，显示了当查询谓词需要搜索表中绝大部分行时，数据库为何倾向于执行全表扫描，条件”department_id>30″显示比”department_id<1200″需要扫描更多的数据。
SQL> select * from emp where department_id>30;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2078 | 7383 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 2078 | 7383 | 23 (0)| 00:00:01 |
--------------------------------------------------------------------------
2 - access("DEPARTMENT_ID">30)
索引降序范围扫描(index range scan descending)操作与索引范围扫描非常类似，不同的是，使用索引降序范围扫描时数据库引擎以降序读取结果。优化器之所以做出这样的选择，其中一个原因可能是避免将来再排序。在指定order by
SQL> select * from emp where department_id<20 order by department_id desc;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400 101 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3507836879
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 759 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 11 | 759 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| DEPT_IDX | 11 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"<20)