Sorts of data
After you run EXPLAIN, Db2 sorts are indicated in PLAN_TABLE. The sorts can be either sorts of the composite table or the new table.
If a single row of PLAN_TABLE has
a 'Y' in more than one of the sort
composite columns, then one sort accomplishes two things. (Db2 does
not perform two sorts when two 'Y's are in the same row.) For instance,
if
both SORTC_ORDERBY and SORTC_UNIQ are 'Y' in one row of PLAN_TABLE,
then a
single sort puts the rows in order and removes any duplicate rows
as well.
The only reason Db2 sorts the new table is for join processing, which is indicated by SORTN_JOIN.
Sorts for GROUP BY and ORDER BY
These sorts are indicated by SORTC_ORDERBY, and SORTC_GROUPBY in PLAN_TABLE.
If the statement includes both a GROUP BY clause and an ORDER BY clause, and if every item in the ORDER-BY list is in the GROUP-BY list, then only one sort is performed, which is marked as SORTC_ORDERBY.
The performance of the sort by the GROUP BY clause is improved when the query accesses a single table and when the GROUP BY column has no index.
Sorts to remove duplicates
This type of sort is used to process a query with SELECT DISTINCT, with a set function such as COUNT(DISTINCT COL1), or to remove duplicates in UNION processing. It is indicated by SORTC_UNIQ in PLAN_TABLE.
Sorts used in join processing
For hybrid join (METHOD 4) and nested loop join (METHOD 1), the composite table can be sorted to make the join more efficient. For merge join (METHOD 2), both the composite table and new table need to be sorted unless an index is used for accessing these tables that gives the correct order already. The sorts needed for join processing are indicated by SORTN_JOIN and SORTC_JOIN in the PLAN_TABLE.
When SORTN_JOIN is set, each of the following join method behaves differently:
- Nested loop join
- SORTN_JOIN is only valid in support of star join. When SORTN_JOIN = Y for nested loop join, the qualified rows from the dimension or snowflake are sorted into the fact table join column sequence. A sparse index might also be created as a result of the sort to support efficient feedback loop skipping processing which is part of the star-join execution.
- Sort merge join
- The new table is accessed and sorted into join column sequence.
- Hybrid join
- When SORTN_JOIN is on, the intermediate table is sorted
into inner table
rid sequence to support efficient list prefetch access to the inner
table
data.
Sorts for subquery processing
When a noncorrelated IN or NOT IN subquery is present in the query, the results of the subquery are sorted and put into a work file for later reference by the parent query.
The results of the subquery are sorted because this allows the parent query to be more efficient when processing the IN or NOT IN predicate. Duplicates are not needed in the work file, and are removed. Noncorrelated subqueries used with =ANY or =ALL, or NOT=ANY or NOT=ALL also need the same type of sort as IN or NOT IN subqueries. When a sort for a noncorrelated subquery is performed, you see both SORTC_ORDERBY and SORTC_UNIQUE in PLAN_TABLE. This is because Db2 removes the duplicates and performs the sort.
SORTN_GROUPBY, SORTN_ORDERBY, and SORTN_UNIQ
are not currently used by Db2.