索引全扫描(INDEX FULL SCAN)

所谓的索引全扫描(INDEX FULL SCAN)就是指要扫描目标索引所有叶子块的所有索引行。这里需要注意的是,索引全扫描需要扫描目标索引的所有叶子块,但这并不意味着需要扫描该索引的所有分支块。在默认情况下,Oracle在做索引全扫描时只需要通过访问必要的分支块定位到位于该索引最左边的叶子块的第一行索引行,就可以利用该索引叶子块之间的双向指针链表,从左至右依次顺序扫描该索引所有叶子块的所有索引行了。

例子一:查询的列有唯一索引,使用索引全扫描(INDEX FULL SCAN)

执行如下SQL:

SCOTT@PDBORCL>set autotrace on;

SCOTT@PDBORCL> select empno from emp;

执行计划如下:

SCOTT@PDBORCL> select empno from emp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

已选择 14 行。


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

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    14 |    56 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------


统计信息
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         68  consistent gets
         17  physical reads
          0  redo size
        703  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)
         14  rows processed

SCOTT@PDBORCL>

image

对于上述SQL(即select empno from emp)而言,表EMP的列EMPNO上存在一个单键值B树主键索引PK_EMP,所以列EMPNO的属性一定是NOT NULL,而该SQL的查询列又只有列EMPNO,所以Oracle此时就可以走对主键索引PK_EMP的索引全扫描。

查询的列为非唯一索引时,不使用索引全扫描(INDEX FULL SCAN)

emp_temp 表和emp表结构相同,只不过empno为非唯一索引

SCOTT@PDBORCL> select empno  from emp_temp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

已选择 14 行。


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

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    14 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP_TEMP |    14 |    56 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
         39  recursive calls
          0  db block gets
         56  consistent gets
         15  physical reads
          0  redo size
        703  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)
         14  rows processed

SCOTT@PDBORCL> select empno  from emp_temp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

已选择 14 行。


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

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    14 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP_TEMP |    14 |    56 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
         39  recursive calls
          0  db block gets
         56  consistent gets
         15  physical reads
          0  redo size
        703  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)
         14  rows processed

SCOTT@PDBORCL>

image

例子二:order by包含唯一索引,使用索引全扫描(INDEX FULL SCAN)


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

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择 14 行。


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

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    14 |   532 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
         66  recursive calls
          0  db block gets
         97  consistent gets
         21  physical reads
          0  redo size
       1647  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)
         14  rows processed

SCOTT@PDBORCL>

image

order by 中为非唯一索引时,不使用索引全扫描(INDEX FULL SCAN)

emp_temp 表和emp表结构相同,只不过empno为非唯一索引

SCOTT@PDBORCL> select *  from emp_temp  order by empno;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择 14 行。


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

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  SORT ORDER BY     |          |    14 |   532 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP_TEMP |    14 |   532 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


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

SCOTT@PDBORCL>

image

posted on 2015-04-29 00:21  小强斋太  阅读(...)  评论(...编辑  收藏

导航