在《》的1.5小节，提到了几种"索引的常见执行计划"：
INDEX FULL SCAN：索引的全扫描，单块读，有序
INDEX RANGE SCAN：索引的范围扫描 INDEX FAST FULL SCAN：索引的快速全扫描，多块读，无序 INDEX FULL SCAN(MIN/MAX)：针对MAX(),MIN()函数的查询 INDEX SKIP SCAN：查询条件没有用到组合索引的第一列，而组合索引的第一列重复度较高时，可能用到
本文用简单的测试案例，体会下索引使用这些执行计划的场景：
1.准备测试环境
创建测试表和索引：
conn jingyu/jingyudrop table test_objects;create table test_objects as select * from all_objects;create index idx_test_objects_1 on test_objects(owner, object_name, subobject_name);create index idx_test_objects_2 on test_objects(object_id);
查看测试表结构：
SQL> desc test_objects; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NOT NULL NUMBER EDITION_NAME VARCHAR2(30)
查看测试表上的索引信息：
SQL> select index_name, column_name, column_position from user_ind_columns where table_name = 'TEST_OBJECTS';INDEX_NAME COLUMN_NAME COLUMN_POSITION------------------------------ ---------------------------------------- ---------------IDX_TEST_OBJECTS_1 OWNER 1IDX_TEST_OBJECTS_1 OBJECT_NAME 2IDX_TEST_OBJECTS_1 SUBOBJECT_NAME 3IDX_TEST_OBJECTS_2 OBJECT_ID 1
分析表并清空测试环境的shared_pool和buffer_cache：
analyze table test_objects compute statistics;alter system flush shared_pool;alter system flush buffer_cache;
2.编写SQL语句
根据不同执行计划的场景，编写SQL语句：
--INDEX RANGE SCAN（索引的范围扫描）SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name = 'DBMS_OUTPUT';--INDEX SKIP SCAN（针对MAX(),MIN()函数的查询）SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';--INDEX FAST FULL SCAN（索引的快速全扫描，多块读，无序）SELECT owner, object_name FROM test_objects;--INDEX FULL SCAN（索引的全扫描，单块读，有序）SELECT owner, object_name FROM test_objects order by 1, 2;--INDEX FULL SCAN (MIN/MAX)（针对MAX(),MIN()函数的查询）SELECT max(object_id) FROM test_objects;
3.实验环境验证
根据2中的SQL分别在实验环境中验证，没有问题，结果如下：
SQL> set autot trace--1. INDEX RANGE SCANSQL> SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name = 'DBMS_OUTPUT';Execution Plan----------------------------------------------------------Plan hash value: 3492129186---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 29 | 3 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| IDX_TEST_OBJECTS_1 | 1 | 29 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("OWNER"='SYS' AND "OBJECT_NAME"='DBMS_OUTPUT')Statistics---------------------------------------------------------- 59 recursive calls 0 db block gets 104 consistent gets 17 physical reads 0 redo size 676 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 2 rows processed--2. INDEX SKIP SCANSQL> SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';Execution Plan----------------------------------------------------------Plan hash value: 1228438998---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 58 | 27 (0)| 00:00:01 ||* 1 | INDEX SKIP SCAN | IDX_TEST_OBJECTS_1 | 2 | 58 | 27 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("OBJECT_NAME"='DBMS_OUTPUT') filter("OBJECT_NAME"='DBMS_OUTPUT')Statistics---------------------------------------------------------- 2 recursive calls 0 db block gets 32 consistent gets 23 physical reads 0 redo size 684 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed--3. INDEX FAST FULL SCANSQL> SELECT owner, object_name FROM test_objects;84311 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2324984732-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 84311 | 2387K| 138 (0)| 00:00:02 || 1 | INDEX FAST FULL SCAN| IDX_TEST_OBJECTS_1 | 84311 | 2387K| 138 (0)| 00:00:02 |-------------------------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 6097 consistent gets 480 physical reads 0 redo size 3509341 bytes sent via SQL*Net to client 62339 bytes received via SQL*Net from client 5622 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 84311 rows processed--4. INDEX FULL SCANSQL> SELECT owner, object_name FROM test_objects order by 1, 2;84311 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2751381935---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 84311 | 2387K| 505 (1)| 00:00:07 || 1 | INDEX FULL SCAN | IDX_TEST_OBJECTS_1 | 84311 | 2387K| 505 (1)| 00:00:07 |---------------------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 6090 consistent gets 0 physical reads 0 redo size 3509341 bytes sent via SQL*Net to client 62339 bytes received via SQL*Net from client 5622 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 84311 rows processed--5. INDEX FULL SCAN (MIN/MAX)SQL> SELECT max(object_id) FROM test_objects;Execution Plan----------------------------------------------------------Plan hash value: 729623451-------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 4 | | || 2 | INDEX FULL SCAN (MIN/MAX)| IDX_TEST_OBJECTS_2 | 1 | 4 | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Statistics---------------------------------------------------------- 2 recursive calls 0 db block gets 5 consistent gets 2 physical reads 0 redo size 534 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed