Index Scan Access Methods

一、Full Scan Access Methods
 A full scan operation makes ultiblock reads. This means that a single IO call will request several blocks instead of just one. The number of blocks requested will vary and can actually range anywhere from one to the number of blocks specified in the db_file_multiblock_read_count parameter. For example, if the parameter is set to 16 and there are 160 blocks in the table, there could be only 10 calls made to get all the blocks.
 I say that only 10 calls could be made because of the following limitations on multiblock read calls.Oracle will read db_file_multiblock_read_count blocks unless reading the full number of blocks
• causes Oracle to have to read blocks that cross an extent boundary. In this case, Oracle will read the blocks up to the extent boundary in one call, then issue another call to read the remainder.
• means a block already in the buffer cache would be read again as part of the multiblock read. Oracle will simply read the blocks up to those not already in memory, then issue another read call that skips those blocks to read the rest. This could mean that a multiblock read might only read one block at a time. For example, let’s say the multiblock read count was 16 and the range of blocks to be read was between block number 1 and 16. If the even numbered blocks had already been placed into the buffer cache, individual single block reads would be done for each odd numbered block in that range. In that case, 8 read calls would be made—one for each block in that range not already in the buffer cache.
• would exceed an operating system limit for multiblock read sizes. This is dependent on your operating system so it can vary.  

二、Index Scan Access Methods
Oracle有如下几种索引扫描方式
• Index Unique Scan
An index unique scan is chosen when a predicate contains a condition using a column defined with a UNIQUE or PRIMARY KEY index. These types of indexes guarantee that only one row will ever be returned for a specified value.  

• Index range scan
An index range scan is chosen when a predicate contains a condition that will return a range of data. The index can be unique or non-unique as it is the condition that determines whether or not multiple rows will be returned or not. The conditions specified can use operators such as <, >, LIKE, BETWEEN and even =.
• Index Full Scan
An index full scan is chosen under several conditions including: when there is no predicate but the column list can be satisfied through an index on a column, the predicate contains a condition on a non-leading column in an index, or the data can be retrieved via an index in sorted order and save the need for a separate sort step. 是根据索引结构读取的。

• Index Skip Scan
An index skip scan is chosen when the predicate contains a condition on a non-leading column in an index and the leading columns are fairly distinct. In earlier releases of Oracle, if a predicate used a column that wasn’t the leading column in an index, the index couldn’t be chosen. This behavior changed in Oracle version 9 with the introduction of the index skip scan. A skip scan works by logically splitting a multi-column index into smaller subindexes. The number of logical subindexes is determined by the number of distinct values in the leading columns of the index. Therefore, the more distinct the leading columns are, the more logical subindexes would need to be created. If too many subindexes would be required, the operation won’t be as efficient as simply doing a full scan.
However, in the cases where the number of subindexes needed would be smaller, the operation can be many times more efficient than a full scan as scanning smaller index blocks can be more efficient than scanning larger table blocks.

• Index Fast Full Scan
An index fast full scan is more like a full table scan than like other index scan types. When an index fast full scan operation is chosen, all the index blocks are read using multiblock reads. This type of scan is chosen as an alternative to a full table scan when all the columns needed to satisfy the query’s column list are included in the index and at least one column in the index has the NOT NULL constraint.In this case, the data is accessed from the index instead of having to access table blocks. Unlike other index scan types, the index fast full scan cannot be used to avoid a sort since the blocks are read using unordered multiblock reads.跟全表扫描一样，是通过物理索引block读取的。

三、Index Full Scan vs Index Fast Full Scan
首先来看一下IFS,FFS能用在哪里： 在一句sql中，如果我们想搜索的列都包含在索引里面的话，那么index full scan 和 index fast full scan 都可以被采用代替full table scan。比如以下语句：
SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
SQL> CREATE INDEX ind_test_id ON TEST(object_id);
SQL> INSERT INTO TEST
SELECT *
FROM dba_objects
WHERE object_id IS NOT NULL AND object_id > 10000
ORDER BY object_id DESC;

SQL> analyze table test compute statistics for table for all columns for all indexes;

Table analyzed.

SQL> set autotrace trace;

SQL> select object_id from test;

17837 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=68 Card=17837 Bytes=71348)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=68 Card=17837 Bytes=71348)

这时候 Oracle会选择全表扫描，因为 object_id 列默认是可以为null的，来修改成 not null:
SQL>alter table test modify(object_id not null);

SQL> select object_id from test;

17837 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=17837 Bytes=71348)
1 0 INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)

在看下面的查询
SQL> select object_id from test order by object_id;

17837 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
  1 0 INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)

我们看到了，前者使用了IFFS，后者使用了IFS。因为前者对查询结果排序没有要求，所以可以根据物理索引快多块读取提高速度，而后者对查询结果要求排序，所以必须根据索引结构进行查询，那样查询出来的结果就排好序了。