索引全扫描,顾名思义就是索引段要被全部扫描。
SQL> select empno,ename from emp order by empno asc;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 4170700152
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
710 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select empno,ename from emp order by empno desc;
EMPNO ENAME
---------- ----------
7934 MILLER
7902 FORD
7900 JAMES
7876 ADAMS
7844 TURNER
7839 KING
7788 SCOTT
7782 CLARK
7698 BLAKE
7654 MARTIN
7566 JONES
7521 WARD
7499 ALLEN
7369 SMITH
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3088625055
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| PK_EMP | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
710 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
由于索引本身是排序的,所以我们对empno字段进行order by时,就会对索引段进行全扫描,但是描述的步骤是根据叶子节点之间链表进行的。
所以index full scan实际上是利用链表来对叶子节点一个一个地读取。