Index lookup
Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.
Index unique scan This is a method for looking up a single key value via a unique index and it 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 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. It can be used for range operations (e.g. > < >= <= between ) or where the data to be returned is not unique.
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 chosen when statistics indicate that it is going to be more efficient than a Full table scan and a sort. The index blocks are scanned one by one, not using multi-block I/O (like a FTS or Index FFS). 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. In the following example, Index BE_IX is a concatenated index on emp (empno,ename). A select with no predicates results in an index full scan since it can satisfy the whole query without need to visit the table.
Index Fast Full Scan (Index FFS) An Index Fast Full Scan (Index FFS) scans all the blocks in the index using multiblock I/O. This means that the rows are not necessarily returned in sorted order. Index FFS may be hinted using INDEX_FFS hint and can be executed in parallel. It can also be used to access second column of concatenated indexes because the whole index is being retrieved as compared to a range scan which may not retrieve all the blocks. Note that INDEX FAST FULL SCAN is the mechanism behind fast index create and recreate. We can use the E_CIX concatenated index to illustrate an Index FFS. Index FFS can also be used to access second column of concatenated indexes because the whole index is being retrieved as compared to a range scan which may not retrieve all the blocks.
Index skip scan 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. The next example checks ename=’SMITH’ for each index key even though ename is not the leading column of the index. The leading column (empno) is skipped.
