表访问方式

1.Full Table Scan (FTS)  全表扫描

2.Index Lookup  索引扫描

There are 5 methods of index lookup:

index unique scan    -- 索引唯一扫描

Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.

 

index range scan    -- 索引局部扫描

Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .

 

index full scan    -- 索引全局扫描

Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.

 

index fast full scan    -- 索引快速全局扫描，不带order by 情况下常发生

Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

 

index skip scan    -- 索引跳跃扫描，where 条件列是非索引的前导列情况下常发生

Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.

 

3.Rowid  物理ID 扫描

This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid 扫描是最快的访问数据方式