时间：2021-07-01 10:21:17 帮助过：6人阅读
索引唯一扫描（INDEX UNIQUE SCAN）
索引唯一扫描只发生在唯一性索引（UNIQUE INDEX）上，它仅仅适用于WHERE 条件中是等值查询的SQL，因为对于唯一索引，等值查询至多只会返回一条记录。对于组合唯一索引来说，WHERE条件需要包含所有的索引列才能使用索引唯一扫描（INDEX UNIQUE SCAN）。
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM SCOTT.EMP
2 WHERE EMPNO=7788;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
891 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
索引唯一扫描(INDEX UNIQUE SCAN)仅仅适用于WHERE条件中是等值查询的SQL， 如果是查询条件是一个区间范围，则不会使用索引唯一扫描。如下所示，执行计划变成了索引范围扫描（INDEX RANGE SCAN）。
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM SCOTT.EMP
2 WHERE EMPNO>=788 AND EMPNO <=7788;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 169057108
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 418 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 418 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 11 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">=788 AND "EMPNO"<=7788)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1383 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
注意：如下所示，如果查询条件为DEPTNO下的等值查询，由于字段DEPTNO上的索引为IX_DEPTNO（非唯一索引），所以不会出现索引唯一扫描。也就是说索引唯一扫描只发生在唯一索引上。
SQL> SELECT * FROM SCOTT.EMP
2 WHERE DEPTNO=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 1198124189
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_DEPTNO | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
1 physical reads
0 redo size
1159 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
对于组合唯一索引，WHERE条件需要包含所有的索引列才能使用索引唯一扫描。否则就会使用索引范围扫描（INDEX RANGE SCAN）
SQL> CREATE TABLE SCOTT.EMP_TEST
2 AS
3 SELECT * FROM SCOTT.EMP;
Table created.
SQL> CREATE UNIQUE INDEX SCOTT.IDX_EMP_TEST_U ON SCOTT.EMP_TEST(EMPNO, ENAME);
Index created.
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM SCOTT.EMP_TEST
2 WHERE EMPNO=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 206749441
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_TEST_U | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT * FROM SCOTT.EMP_TEST
2 WHERE EMPNO=7788 AND ENAME=‘SCOTT‘;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2899991080
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_EMP_TEST_U | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788 AND "ENAME"=‘SCOTT‘)
SQL>
对于组合唯一索引，如果索引前导列不在WHERE条件中，那么执行计划路径就会走全表扫描而不会走索引扫描，如下所示：
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM SCOTT.EMP_TEST
2 WHERE ENAME=‘SCOTT‘;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3124080142
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_TEST | 1 | 38 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"=‘SCOTT‘)
SQL>
另外，在执行计划中，索引唯一扫描的下一步，你会注意到有可能是“TABLE ACCESS BY INDEX ROWID”或“SELECT STATEMENT”部分。 “TABLE ACCESS BY INDEX ROWID"这表示表数据没有通过FTS操作访问，而是通过rowid查找访问。 如果所有必需的数据都驻留在索引中，则表查找可能是不必要的，您将看到的只是索引访问(没有表访问)。 在以下示例中，所有列（只有EMPNO列）都在索引中。 所以，它不会进行表访问，而使用SELET * 就会产生表访问：
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT EMPNO FROM SCOTT.EMP
2 WHERE EMPNO=7788;
EMPNO
----------
7788
Execution Plan
----------------------------------------------------------
Plan hash value: 56244932
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=7788)
SQL> SELECT * FROM SCOTT.EMP
2 WHERE EMPNO=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
SQL>
通常，执行计划先通过Index查找到数据对应的rowid值（对于非唯一索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据，这种查询方式称为索引扫描或索引查找（index loopup）。一个rowid唯一表示一行数据，该行对应的数据块是通过一次I/O得到的，在此情况下该次I/O只会读取一个数据块。在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID的值。
索引扫描可以由2步组成：
扫描索引得到对应的ROWID值
通过找到的ROWID从表中读取具体的数据
每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第一步的I/O经常为逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是一个物理操作，相对逻辑I/O来说，是极其费时间的。所以如果对大表进行索引扫描，取出的数据如果大于总量的5%-10%，使用索引扫描会效率下降很多。但是如果查询的数据能全在索引中找到，就可以避免进行第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很高的。进一步讲，如果SQL语句中对索引列进行排序，因为索引已经预先排序好了，所以在执行计划中不需要再次对索引列进行排序。
索引范围扫描（INDEX RANGE SCAN）
索引范围扫描是一种很常见的表访问方式，索引范围扫描的典型情况下是在谓词（WHERE限制条件）中使用范围操作符（<,>,<>,>=,<=,BEWTEEN）。下面是发生索引范围扫描的一些场景
1： 在唯一索引上使用范围查找操作符（>, <, <>, >=, <=, BEWTEEN）等。
2： 在组合唯一索引上，只使用部分列进行查询（一定包含前导列leading column)，导致查询出多条记录（也有可能是一条记录）。
3： 在非唯一索引列上进行任何查询。
1： 在唯一索引上使用范围查找操作符（>, <, <>, >=, <=, BEWTEEN）等。
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM SCOTT.EMP
2 WHERE EMPNO >=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 169057108
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 5 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">=7788)
SQL>
2： 在组合唯一索引上，只使用部分列进行查询(一定包含前导列leading column )，导致查询出一条或多条记录
SQL> SET AUTOTRACE OFF;
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM SCOTT.EMP_TEST
2 WHERE EMPNO=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 206749441
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_TEST_U | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
SQL>
3： 在非唯一索引列上进行任何查询。
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM SCOTT.EMP
2 WHERE DEPTNO=20;
Execution Plan
----------------------------------------------------------
Plan hash value: 1198124189
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_DEPTNO | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=20)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1241 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> SELECT * FROM SCOTT.EMP
2 WHERE DEPTNO >=10;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1198124189
-----------------------------------------------------------------------------------------
| 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 RANGE SCAN | IX_DEPTNO | 14 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO">=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1630 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
索引降序范围扫描（INDEX RANGE SCAN DESCENDING）
在默认情况下，索引是按照索引关键字升序来存放数据的。当SQL语句使用ORDER BY COLUMN_NAME DESC排序时，就会出现INDEX RANGE SCAN DESCENDING的访问路径
按照常理来讲，如果有ORDER BY COLUMN_NAME DESC的条件，那么
① 需要先从索引中读取数据；
② 再按照条件中COLUMN_NAME字段做降序排序。
而选择INDEX RANGE SCAN DESCENDING，则是直接在索引上按照索引关键字降序查找数据（其实是找到对应索引最右边的叶子块的第一行索引行，然后通过叶子块之间的双向链表访问数据），这样正是为了避免先按照索引来查找数据，然后再做一次降序排序的操作。如下测试所示：
SQL> SET LINESIZE 1200;
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM SCOTT.EMP_TEST
2 WHERE EMPNO >= 7788
3 ORDER BY EMPNO DESC;
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4203867900
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP_TEST | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_EMP_TEST_U | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">=7788 AND "EMPNO" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1324 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
SQL>
索引全扫描（INDEX FULL SCAN）
索引全扫描需要扫描目标索引所有叶子块的所有索引行。但是这不意味着索引全扫描需要扫描该索引的所有分支块。在默认情况下，索引全局扫描只需要通过访问必要的分支块定位到该索引最左边的叶子块的第一行索引行。然后就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索引所有叶子块的所有索引行了。索引全扫描的结果是有序的。它是根据索引的键值列来排序的。也就是说如果走索引全局扫描能达到排序的效果。这样就可以避免对该索引的索引键值再做排序操作。但是，也正是索引全扫描的有序性决定了索引全扫描不能够并行执行。如果查询列全部是目标索引的索引列，那么索引全扫描是不需要回表的。
SQL> CREATE INDEX SCOTT.IX_EMP_N1 ON SCOTT.EMP(EMPNO, ENAME);
Index created.
SQL> SET AUTOTRACE TRACEONLY;
SQL> select empno, ename from scott.emp order by empno,ename;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 162731191
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IX_EMP_N1 | 14 | 140 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
837 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
索引快速扫描（INDEX FAST FULL SCAN）
索引快速扫描和索引全扫描一样，也需要扫描目标索引所有的叶子块的所有索引行。它也适用于所有类型的B树索引（包括唯一性索引和非唯一性索引）。 索引快速全扫描与索引全扫描相比有以下三点区别：
1： 索引快速全扫描只适用于CBO模式，而索引全扫描可以用于CBO也可以用于RBO。
2： 索引快速全扫描可以使用多块读，也可以并行执行。这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间
3： 索引快速全扫描的执行结果不一定是有序的，因为索引快速全扫描是根据索引在磁盘上的物理存储顺序来扫描的，而不是根据索引行的逻辑顺序来扫描的。所以扫描结果不一定有序（对于单个索引叶子块中的索引行而言，其物理存储顺序和逻辑存储顺序是一致的；但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序）
SQL> BEGIN
2 FOR IndexLoop IN 0..1000 LOOP
3 INSERT INTO SCOTT.EMP(EMPNO,ENAME)
4 VALUES(IndexLoop,CONCAT(‘TEST‘,IndexLoop));
5 END LOOP;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats(ownname => ‘SCOTT‘, tabname =>‘EMP‘,
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO‘);
PL/SQL procedure successfully completed.
SQL> SELECT EMPNO FROM SCOTT.EMP;
1015 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 366039554
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1015 | 4060 | 3 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_EMP | 1015 | 4060 | 3 (0)| 0