概述
今天主要介绍下Oracle索引的常见执行计划：
- INDEX FULL SCAN：索引的全扫描，单块读，有序
- INDEX RANGE SCAN：索引的范围扫描
- INDEX FAST FULL SCAN：索引的快速全扫描，多块读，无序
- INDEX FULL SCAN(MIN/MAX)：针对MAX(),MIN()函数的查询
- INDEX SKIP SCAN：查询条件没有用到组合索引的第一列，而组合索引的第一列重复度较高时，可能用到。
这里用简单的测试案例，体会下索引使用这些执行计划的场景。
1. 准备测试环境
创建测试表和索引：
drop 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);
desc test_objects;
- 1.
- 2.
- 3.
- 4.
- 5.
查看测试表上的索引信息：
select index_name, column_name, column_position from user_ind_columns where table_name = 'TEST_OBJECTS';
- 1.
分析表并清空测试环境的shared_pool和buffer_cache：
analyze table test_objects compute statistics;
alter system flush shared_pool;
alter system flush buffer_cache;
- 1.
- 2.
- 3.
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;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
3. 实验论证
结果如下：
(1) INDEX RANGE SCAN
set autotrace traceonly
SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name = 'DBMS_OUTPUT';
- 1.
- 2.
(2) INDEX SKIP SCAN
SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
- 1.
(3) INDEX FAST FULL SCAN
SELECT owner, object_name FROM test_objects;
- 1.
(4) INDEX FULL SCAN
SELECT owner, object_name FROM test_objects order by 1, 2;
- 1.
(5) INDEX FULL SCAN (MIN/MAX)
SELECT max(object_id) FROM test_objects;
- 1.
