索引全扫描（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>
对于上述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>
例子二：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>
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>
