My personal most favorite resource for execution plan operations is Julian Dyke’s listing—however, that is from a different point of view.
Index and Table Access
- INDEX UNIQUE SCAN
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”.- INDEX RANGE SCAN
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.- INDEX FULL SCAN
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 anINDEX FAST FULL SCAN
and perform an additional sort operation. See Chapter 6, “Sorting and Grouping”.- INDEX FAST FULL SCAN
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
, theINDEX FAST FULL SCAN
can benefit from multi-block read operations. See Chapter 5, “Clustering Data: The Second Power of Indexing”.- TABLE ACCESS BY INDEX ROWID
Retrieves a row from the table using the
ROWID
retrieved from the preceding index lookup. See also Chapter 1, “Anatomy of an SQL Index”.- TABLE ACCESS FULL
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”.
Support My Work
I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.
Joins
Generally join operations process only two tables at a time. In case a query has more joins, they are executed sequentially: first two tables, then the intermediate result with the next table. In the context of joins, the term “table” could therefore also mean “intermediate result”.
- NESTED LOOPS JOIN
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”.
- HASH JOIN
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”.
- MERGE JOIN
The merge join combines two sorted lists like a zipper. Both sides of the join must be presorted. See also “Sort Merge”.
Sorting and Grouping
- SORT ORDER BY
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”.- SORT ORDER BY STOPKEY
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”.- SORT GROUP BY
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”.- SORT GROUP BY NOSORT
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”.- HASH 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”.
Top-N Queries
The efficiency of top-N queries depends on the execution mode of the underlying operations. They are very inefficient when aborting non-pipelined operations such as SORT ORDER BY
.
- COUNT STOPKEY
Aborts the underlying operations when the desired number of rows was fetched. See also “Querying Top-N Rows”.
- WINDOW NOSORT STOPKEY
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”.