索引范围扫描(INDEX RANGE SCAN)

      索引范围扫描(INDEX RANGE SCAN)适用于所有类型的B树索引,当扫描的对象是唯一性索引时,此时目标SQL的where条件一定是范围查询(谓词条件为 BETWEEN、<、>等);当扫描的对象是非唯一性索引时,对目标SQL的where条件没有限制(可以是等值查询,也可以是范围查询)。 索引范围扫描的结果可能会返回多条记录,其实这就是索引范围扫描中"范围"二字的本质含义。

测试一:唯一索引的范围查询

SCOTT@PDBORCL> select * from emp where empno>7369;

已选择 13 行。


执行计划
----------------------------------------------------------
Plan hash value: 2787773736

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |    13 |   494 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |    13 |   494 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PK_EMP |    13 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO">7369)


统计信息
----------------------------------------------------------
         54  recursive calls
          0  db block gets
         97  consistent gets
         21  physical reads
          0  redo size
       1607  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
         13  rows processed

SCOTT@PDBORCL>
View Code

image

SCOTT@PDBORCL>  select * from emp where empno>7369 order by empno desc; (降序排列)

SCOTT@PDBORCL>  select * from emp where empno>7369 order by empno desc;

已选择 13 行。


执行计划
----------------------------------------------------------
Plan hash value: 2707196548

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    13 |   494 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMP    |    13 |   494 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| PK_EMP |    13 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO">7369)


统计信息
----------------------------------------------------------
         54  recursive calls
          0  db block gets
         97  consistent gets
         21  physical reads
          0  redo size
       1607  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
         13  rows processed

SCOTT@PDBORCL>
View Code

这个执行计划和上面的执行计划有什么区别呢?其中第一个执行计划是索引范围扫描(INDEX RANGE SCAN),但是第二个执行计划是索引范围降序扫描(INDEX RANGE SCAN DESCENDING)。由此可以看出对于唯一索引,ORACLE是已经进行了正序排列了。也就是唯一索引在进行排序的时候消耗不是很大的,因为它在保存 的时候就按照升序进行保存的。

测试二:非唯一索引的等值查询

创建一个测试表EMP_TEMP:

create table emp_temp as select * from emp;

然后在表EMP_TEMP的列EMPNO上创建一个单键值非唯一性同名B树索引IDX_EMP_TEMP:

create index idx_emp_temp on emp_temp(empno);

最后我们再次执行如下SQL:

select * from emp_temp where empno=7369;

执行计划如下:

SCOTT@PDBORCL> create table emp_temp as select * from emp;

表已创建。

SCOTT@PDBORCL> create index idx_emp_temp on emp_temp(empno);

索引已创建。

SCOTT@PDBORCL> select * from emp_temp where empno=7369;


执行计划
----------------------------------------------------------
Plan hash value: 1638992559

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEMP     |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_EMP_TEMP |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7369)


统计信息
----------------------------------------------------------
         46  recursive calls
          0  db block gets
         76  consistent gets
         16  physical reads
          0  redo size
       1042  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SCOTT@PDBORCL>
View Code

image

此SQL的执行计划已经从之前的索引唯一性扫描变为现在的索引范围扫描

测试三:非唯一索引的范围查询

同样是测试二中的表EMP_TEMP

SCOTT@PDBORCL> select * from emp_temp where empno>7369;

执行计划如下:

SCOTT@PDBORCL> select * from emp_temp where empno>7369;

已选择 13 行。


执行计划
----------------------------------------------------------
Plan hash value: 1638992559

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    13 |   494 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEMP     |    13 |   494 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_EMP_TEMP |    13 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO">7369)


统计信息
----------------------------------------------------------
         46  recursive calls
          0  db block gets
         77  consistent gets
         15  physical reads
          0  redo size
       1607  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         13  rows processed

SCOTT@PDBORCL>
View Code

image

posted on 2015-04-28 22:57  小强斋太  阅读(...)  评论(...编辑  收藏

导航