Oracle query plan explonation
{< author “Tereschenko M.” >}}
This article describes the main operations displayed in the query execution plans of Oracle.
Index Unique Scan
Performs a B-tree traversal only. This operation is used when a unique constraint ensures that at most one record will match the search criteria.
Index Range Scan
Performs a B-tree traversal and scans chains of leaf nodes to find all matching records.
Index Full Scan
Reads the entire index (all rows) in the order represented by the index. Depending on various system statistics, the DBMS may perform this operation if all rows are needed in index order, for example because of a corresponding ORDER BY clause. The optimizer may also use an Index Fast Full Scan operation instead and perform an additional sort operation.
Index Fast Full Scan
Reads the entire index (all rows) in on-disk order. This operation is typically performed instead of a full table scan if all required columns are available in the index. Like the TABLE ACCESS FULL operation, INDEX FAST FULL SCAN can benefit from multi-block reads.
Table Access By Index ROWID
Retrieves a row from the table using the ROWID obtained from a previous index seek.
Table Access Full
A full table scan. Reads the entire table (all rows and columns), in on-disk order. Although multi-block reads significantly improve the speed of a full table scan, it is still one of the most expensive operations. In addition to the high I/O costs, a full table scan must examine all rows in the table, which also takes a significant amount of CPU time.
Merge Join
A merge join combines two sorted lists. Both sides of the join must be pre-sorted.
Nested Loops
A nested loop join combines two tables by selecting the result from one table and querying the other table for each row from the first one. It is very common method. It performs a fairly efficient join on relatively small data sets.
A nested loop join does not require the input data to be sorted.
Hash Join
A hash join loads candidate records from one side of the join into a hash table, which is then checked for each row from the other side of the join. It is used whenever other join types cannot be used: when the data sets being joined are large enough and/or the data sets are not ordered by the join columns.
Sort Unique
Sorts rows and eliminates duplicates.
Hash Unique
A more efficient implementation of the sorting and duplicate elimination algorithm using a hash table. Replaces the Sort Unique operation in certain circumstances.
Sort Aggregate
Calculates grand totals using the aggregate functions SUM, COUNT, MIN, MAX, AVG, etc.
Sort Order By
Sorts the result according to the ORDER BY clause. This operation requires large amounts of memory to materialize the intermediate result.
Sort Group By
Sorts the recordset by the GROUP BY columns and aggregates the sorted result in a second step. This operation requires large amounts of memory to materialize the intermediate result.
Sort Group By Nosort
Aggregates the pre-sorted recordset according to the GROUP BY clause. This operation does not buffer the intermediate result.
Hash Group By
Groups the result using a hash table. This operation requires large amounts of memory to materialize the intermediate set of records. The output is not ordered in any meaningful way.
Filter
Applies a filter to a set of rows.
View
Creates an intermediate view of the data.
Count Stopkey
Stops the operation when the desired number of rows have been selected.
Sort Join
Sorts the set of records in the join column. Used in conjunction with the Merge Join operation to perform a sort on a merge join.
Intersection
Performs an intersection operation on two data sources.## Count Stopkey
Stops the operation when the desired number of rows have been selected.
Sort Join
Sorts the set of records in the join column. Used in conjunction with the Merge Join operation to perform a sort on a merge join.
Intersection
Performs an intersection operation on two data sources.
Union-All
Performs a union operation of all records of two tables. Duplicate rows are not removed.
Load As Select
Direct loading using a SELECT statement as the source.
Temp Table Generation/Transformation
Creates/transforms a temporary table. Used in Oracle-specific Star transformations.