常见的index 相关的扫描方式大概有如下几种：
index range scan(索引范围扫描)：
1.对于unique index来说,如果where 条件后面出现了<,> ,between ...and...的时候,那么就可能执行index range scan,如果where条件后面是=,那么就会执行index unique scan。
2.对于none unique index来说 如果where 条件后面出现了=,>,<,betweed...and...的时候,就有可能执行index range scan。
3.对于组合索引来说,如果where条件后面出现了组合索引的引导列,那么可能执行index range scan。
index rang scan 是根据索引的叶子block中数据去访问表,和 key 大小顺序一致
index skip scan(索引跳跃式扫描)
当查询可以通过组合索引得到结果,而且返回结果很少,并且where条件中没有包含索引引导列的时候,可能执行index skip scan
索引跳跃式扫描发生的条件:
1.必须是组合索引
2.引导列没有出现在where条件中
index fast full scan(索引快速全扫描)：
index ffs 是说如果可以从INDEX上获取select后面所有的列数据,且where 条件没有组合索引的前导列,可能执行index ffs,不需要去访问表；如果不可以从index上获取某次访问的数据,它可能会执行INDEX FULL SCAN,然后再通过ROWID去访问DATA BLOCK!
发生的条件
1.必须是组合索引
2.组合索引的引导列不在where条件中
3 要返回的数据较多(无定性)
index fast full scan 是根据索引segment的process
iis7站长之家去搜索的,FFS跟FTS的原理类似,只是扫描index segment 而不是FTS 的table segment,一次读可以是连续的多个index block,因此这样出来的数据顺序和索引顺序并不一致。
而我们通常说的利用不上索引指的是 index range scan or other index scan,不是 index fast full scan 。
index fast full scan的前提是就像数据肯定存在索引中有(比如not null 的字段,或者复合索引,bitmap索引等),然后索引segment比表segment小,通过索引segment能得到所需要数据,而不用去读任何表的block,这样IO将减少。
环境：使用tom的bigtable 脚本创建一个表 并创建如下索引：
www.linuxidc.com@linuxidc> @bigtab
www.linuxidc.com@linuxidc> create index ind_owner on bigtab(owner);
Index created.
www.linuxidc.com@linuxidc> create index ind_type on bigtab(object_type);
Index created.
www.linuxidc.com@linuxidc> create index ind_ooo on bigtab(owner,object_name,object_type);
Index created.
收集统计信息：
www.linuxidc.com@linuxidc> exec dbms_stats.gather_table_stats('yang','bigtab',cascade=>true, method_opt=>'for all columns');
PL/SQL procedure successfully completed.
其中 字段id 为主键，并建立索引 idx_id！
www.linuxidc.com@linuxidc> set autot on
www.linuxidc.com@linuxidc> set autot trace
当where 条件是 非等于号时，cbo会选择index range scan
www.linuxidc.com@linuxidc> select id from bigtab where id < 10;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2204167725
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| BIGTAB_PK | 1 | 5 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"<10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
index range scan 是 根据叶子节点的顺序去寻找数据，数据出来和索引顺序是一致的排好顺序的，一次读一个索引block和一个数据block，从上面的逻辑读中可以看出来2个逻辑读！
当where条件中对唯一索引使用等号过滤是 ，cbo选择INDEX UNIQUE SCAN
www.linuxidc.com@linuxidc> select id from bigtab where id = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 794512637
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| BIGTAB_PK | 1 | 5 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=10)
由于owner 是非唯一性索引，所以cbo选择了IND_OWNER 并INDEX RANGE SCAN的执行计划
www.linuxidc.com@linuxidc> select owner,object_name ,object_type from bigtab where wner='YANG';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 272829004
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIGTAB | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OWNER | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='YANG')
之前在owner,object_name ,object_type上面建立了组合索引IND_OOO，下面where后面没有使用前导列并且数据量占总数据的40%，因此执行计划选择了INDEX FAST FULL SCAN！
www.linuxidc.com@linuxidc> select owner,object_name ,object_type from bigtab where OBJECT_TYPE='TABLE';
22700 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2535972880
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22862 | 625K| 71 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IND_OOO | 22862 | 625K| 71 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
object_name为 t1的记录只有一条，且 where条件没有使用前导列，故执行计划使用 INDEX SKIP SCAN！
www.linuxidc.com@linuxidc> select owner,object_name ,object_type from bigtab where OBJECT_NAME='T1';
1 rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2709512398
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 1400 | 6 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IND_OOO | 50 | 1400 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='T1')
filter("OBJECT_NAME"='T1')
www.linuxidc.com@linuxidc> select owner,object_name ,object_type from bigtab where OBJECT_NAME='T1' AND OBJECT_TYPE='YANG';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2367137367
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| BIGTAB | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TYPE | 12 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T1')
2 - access("OBJECT_TYPE"='YANG')
我们来看官方描述 for 11.2.0.2 ：
11.2.3.3
This scan returns, at most, a single rowid. Oracle Database performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.
Index Range Scans
An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.
If you require the data to be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.
In , the order has been imported from a legacy system, and you are querying the order by the reference used in the legacy system. Assume this reference is the order_date.
Index Skip Scans
Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
The database determines the number of logical subindexes by the number of distinct values in the initial column. Skip scanning is advantageous when there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.
The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate.
Fast Full Index Scans :
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.
Full Table Scans :
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause.
When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.