2011-07-05Oracle Execution Plan Operations

The INDEX UNIQUE SCAN performs the B-tree traversal only. The database uses this operation if a unique constraint ensures that the search criteria will match no more than one entry. See also Chapter 1, “Anatomy of an SQL Index”.

The INDEX RANGE SCAN performs the B-tree traversal and follows the leaf node chain to find all matching entries. See also Chapter 1, “Anatomy of an SQL Index”.

The so-called index filter predicates often cause performance problems for an INDEX RANGE SCAN. The next section explains how to identify them.

Reads the entire index—all rows—in index order. Depending on various system statistics, the database might perform this operation if it needs all rows in index order—e.g., because of a corresponding order by clause. Instead, the optimizer might also use an INDEX FAST FULL SCAN and perform an additional sort operation. See Chapter 6, “Sorting and Grouping”.

Reads the entire index—all rows—as stored on the disk. This operation is typically performed instead of a full table scan if all required columns are available in the index. Similar to TABLE ACCESS FULL, the INDEX FAST FULL SCAN can benefit from multi-block read operations. See Chapter 5, “Clustering Data: The Second Power of Indexing”.

Retrieves a row from the table using the ROWID retrieved from the preceding index lookup. See also Chapter 1, “Anatomy of an SQL Index”.

This is also known as full table scan. Reads the entire table—all rows and columns—as stored on the disk. Although multi-block read operations improve the speed of a full table scan considerably, it is still one of the most expensive operations. Besides high IO rates, a full table scan must inspect all table rows so it can also consume a considerable amount of CPU time. See also “Full Table Scan”.

Joins two tables by fetching the result from one table and querying the other table for each row from the first. See also “Nested Loops”.

The hash join loads the candidate records from one side of the join into a hash table that is then probed for each row from the other side of the join. See also “Hash Join”.

The merge join combines two sorted lists like a zipper. Both sides of the join must be presorted. See also “Sort Merge”.

Sorts the result according to the order by clause. This operation needs large amounts of memory to materialize the intermediate result (not pipelined). See also “Indexing Order By”.

Sorts a subset of the result according to the order by clause. Used for top-N queries if pipelined execution is not possible. See also “Querying Top-N Rows”.

Sorts the result set on the group by columns and aggregates the sorted result in a second step. This operation needs large amounts of memory to materialize the intermediate result set (not pipelined). See also “Indexing Group By”.

Aggregates a presorted set according the group by clause. This operation does not buffer the intermediate result: it is executed in a pipelined manner. See also “Indexing Group By”.

Groups the result using a hash table. This operation needs large amounts of memory to materialize the intermediate result set (not pipelined). The output is not ordered in any meaningful way. See also “Indexing Group By”.

Aborts the underlying operations when the desired number of rows was fetched. See also Section 1.

Uses a window function (over clause) to abort the execution when the desired number of rows was fetched. See also “Using Window Functions for Efficient Pagination”.