Index Full Scan && Index Range Scan

Index Full Scan reads the whole index, in an ordered manner (i.e. it "walks" the index from the lowest value to the highest value).
(Index Fast Full Scan reads the whole index, without bothering about ordering, it just reads all the index leaf blocks using multiblock reads -- similar to a Table Full Scan).

Index Range Scan reads (or expects to read) a limited set of entries from the index. It starts from a certain point in the index and ends at a certain point in the index.

Thus, Index Full Scan reads all the letters from A to Z and reads them in order.
Index Fast Full Scan reads all the letters from A to Z but doesn't care to read them in order, it just wants all the letters.
Index Range Scan may be reading from the letter C to the letter G only.

index索引的8种使用模式
索引的使用对数据库的性能有巨大的影响。
共有五类不同的使用模式。

1。INDEX UNIQUE SCAN 效率最高，主键或唯一索引
2。INDEX FULL SCAN 有顺序的输出，不能并行读索引
问题：如果表中建立了多个索引，Oracle是把所有的索引都扫描一遍么？

3。INDEX FAST FULL SCAN 读的最块，可以并行访问索引，但输出不按顺序
4。INDEX RANGE SCAN 给定的区间查询
5。INDEX SKIP SCAN 联合索引，不同值越少的列，越要放在前面
6. SCAN DESCENDING
7. index join
8. bitmap join

--实验后的总论。
能用唯一索引，一定用唯一索引
能加非空，就加非空约束
一定要统计表的信息，索引的信息，柱状图的信息。
联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面
只有做到以上四点，数据库才会正确的选择执行计划。
conn system/manager
grant select any dictionary to scott;

conn scott/tiger
drop table t1 purge;
create table t1 as select * from dba_objects;
analyze table t1 compute statistics;
create index it1 on t1(object_type);
set autot traceonly

select distinct object_type from t1;
将是全表扫描，为什么不使用索引呢？因为索引中不能含有null值，
如果使用索引就可能产生不正确的结果。

--增加非空约束
alter table t1 modify (object_type not null);
select distinct object_type from t1 ;
使用INDEX FAST FULL SCAN方式查找数据

--
select object_type from t1;
使用INDEX FAST FULL SCAN，因为不需要排序

select object_type from t1 order by 1;
使用INDEX FULL SCAN,因为要按照顺序输出

select object_type from t1 where object_type='TABLE';
使用INDEX RANGE SCAN

--使用非唯一索引
create index i2t1 on t1(object_id);
select * from t1 where object_id=3762;
使用INDEX RANGE SCAN,因为数据库不知道是否唯一

--使用唯一索引
drop index i2t1;
create unique index i2t1 on t1(object_id);
使用INDEX UNIQUE SCAN,因为数据库知道是唯一的

--跳跃的扫描索引
create index i3t1 on t1(object_type,object_name);
select * from t1 where object_name='EMP';
select object_name from t1 where object_name='EMP';
使用INDEX SKIP SCAN，因为数据库知道可以跳过object_type,虽然object_name在第二个列。

--联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面
drop index i3t1;
drop index it1;
create index i3t1 on t1(object_name,object_type);
select * from t1 where object_type='TABLE';
计划为全表扫描。
----------------------------------------------------------------------
6. SCAN DESCENDING
降序
SQL> SELECT * FROM EMP ORDER BY 1 DESC;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=12 Bytes=384)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=12 Bytes=384)
2 1 INDEX (FULL SCAN DESCENDING) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=12)

正序
SQL> SELECT * FROM EMP ORDER BY 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=12 Bytes=384)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=12 Bytes=384)
2 1 INDEX (FULL SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=12)
----------------------------------------------------------------------
7. index join
SELECT OBJECT_NAME,OBJECT_TYPE FROM T1
WHERE OBJECT_NAME LIKE 'E%' AND OBJECT_TYPE='TABLE';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=12 Bytes=372)
1 0 VIEW OF 'index$_join$_001' (Cost=9 Card=12 Bytes=372)
2 1 HASH JOIN
3 2 INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE) (Cost=10 Card=12 Bytes=372)
4 2 INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=10 Card=12 Bytes=372)
----------------------------------------------------------------------
8. bitmap join
SQL> SELECT COUNT(*) FROM T1 WHERE OBJECT_NAME LIKE 'E%' OR OBJECT_TYPE='TABLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=31)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 SORT (ORDER BY)
6 5 INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=3)
7 3 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE) (Cost=3)

--实验后的总论。
能用唯一索引，一定用唯一索引
能加非空，就加非空约束
一定要统计表的信息，索引的信息，柱状图的信息。
联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面
只有做到以上四点，数据库才会正确的选择执行计划。
conn system/manager
grant select any dictionary to scott;

conn scott/tiger
drop table t1 purge;
create table t1 as select * from dba_objects;
analyze table t1 compute statistics;
create index it1 on t1(object_type);
set autot traceonly

select distinct object_type from t1;
将是全表扫描，为什么不使用索引呢？因为索引中不能含有null值，
如果使用索引就可能产生不正确的结果。

--增加非空约束
alter table t1 modify (object_type not null);
select distinct object_type from t1 ;
使用INDEX FAST FULL SCAN方式查找数据

--
select object_type from t1;
使用INDEX FAST FULL SCAN，因为不需要排序

select object_type from t1 order by 1;
使用INDEX FULL SCAN,因为要按照顺序输出

select object_type from t1 where object_type='TABLE';
使用INDEX RANGE SCAN

--使用非唯一索引
create index i2t1 on t1(object_id);
select * from t1 where object_id=3762;
使用INDEX RANGE SCAN,因为数据库不知道是否唯一

--使用唯一索引
drop index i2t1;
create unique index i2t1 on t1(object_id);
使用INDEX UNIQUE SCAN,因为数据库知道是唯一的

--跳跃的扫描索引
create index i3t1 on t1(object_type,object_name);
select * from t1 where object_name='EMP';
select object_name from t1 where object_name='EMP';
使用INDEX SKIP SCAN，因为数据库知道可以跳过object_type,虽然object_name在第二个列。

--联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面
drop index i3t1;
drop index it1;
create index i3t1 on t1(object_name,object_type);
select * from t1 where object_type='TABLE';
计划为全表扫描。