Oracle索引范围扫描

索引范围扫描
当数据库需要访问具有高度选择性的数据时,它执行索引范围扫描。数据库按升序返回索引列的值。如果所有行的索引列都是相同的,则按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 desc子句而索引又能满足该子句时,基于成本的优化器将使用索引降序范围扫描操作,从而避免降序操作。索引降序范围扫描操作向后读取索引,以避免按正常次序(升序)读取,然后再执行降序排序操作。

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)

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.