DB2 LUW Execution Plan Operations
A short reference of the most common DB2 LUW execution plan operations. Find the full list in the IBM documentation.
Index and Table Access
IXSCANperforms 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 ("
SARG" predicates) often cause performance problems for an
IXSCAN. The next section explains how to identify them. Similar to Oracle’s family of
INDEX ... SCANoperations.
The absence of
STOPpredicates indicates a full index scan.
last_explainedview indicates a reverse scan in brackets (e.g.,
Retrieves a row from the table using the
RIDretrieved from the preceding index lookup. See also Chapter 1, “Anatomy of an SQL Index”. Similar to Oracle’s
TABLE ACCESS BY INDEX ROWID.
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”.
This operation is used for index merge and, possibly even more often, to prefetch data pages after they have been sorted.
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”.
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”.
A multi-table join (more than two) specifically for data warehouses using a star schema.
Sorting and Grouping
Sorts the result according to the order by clause. This operation needs large amounts of memory to materialize the intermediate result (not pipelined). This operation is also used to establish a required order for
SORTmight remove duplicate rows for a distinct operation. See also “Indexing Order By”.
last_explainedview indicates whether a unique is performed in brackets (e.g.
SORT (UNIQUE)). Top-N sorts are labeled with
TOP-N(e.g., because of fetch first ... rows only).
De-duplicates rows in a pre-sorted set. Used for distinct when the required order can be established without a
SORToperation (e.g., because the
IXSCANdelivers them in the required order). When a
SORToperation is necessary, the
SORToperation itself performs the de-duplication.
Aggregates a set according the group by clause. This operation may be executed using a sort/group-by algorithm or a hash based approach (since v10.1). See also “Indexing Group By”.
last_explainedview indicates the aggregation mode in brackets (e.g.
GRPBY (HASH COMPLETE)).
DB2 LUW does not have execution plan operations that directly relate to top-N clauses such as fetch first ... rows only. However, if a
SORT is performed,
last_explained view indicates the top-N optimization in brackets (e.g.,
In case there is no
SORT operation required, there is no visible mark of the top-n behaviour in the execution plan. However, a sudden drop of the cost value or drop in row count estimates in absence of predicates might give you an idea that there must be a Top-N clause at work.