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, However this may return > 1 row as the uniqueness will not be guaranteed.

• Index range scan

Method for accessing multiple column values You must supply AT LEAST the leading column of the index to access data via the index Can be used for range operations (e.g. > < <> >= <= between) 

• Index Full Scan

In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided for a table). 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. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.

An Index full scan will perform single block i/o's and so it may prove to be inefficient. 

• Index Fast Full Scan

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.

Note that INDEX FAST FULL SCAN is the mechinism behind fast index create and recreate.

• Rowid

This is the quickest access method available Oracle simply retrieves the block specified and extracts the rows it is interested in. Most frequently seen in explain plans as Table access by Rowid 

• Joins

A Join is a predicate that attempts to combine 2 row sources We only ever join 2 row sources together Join steps are always performed serially even though underlying row sources may have been accessed in parallel. Join order - order in which joins are performed

The join order makes a significant difference to the way in which the query is executed. By accessing particular row sources first, certain predicates may be satisfied that are not satisfied by with other join orders. This may prevent certain access paths from being taken.