Text-Only QEP
In a terminal monitor, a QEP is displayed as a tree, where each node has one or two children:
Parent
/ \
Child Child
/
Parent
/ \
Child Child
Only join nodes have two children. Other nodes have a left child only. Information on node types is provided in
Types of Nodes in a QEP.
The tree is read from bottom to top and from left to right, starting with the child nodes at the bottom, going up to the intermediate child nodes, if any, and finally up to the first parent node:
QEPs as Data Flow Trees
The bottom up approach in the tree diagram mirrors the flow of data during the execution of a query plan.
Rows are read in the leaf nodes of the query plan, WHERE clauses are applied to reduce the number of rows as soon as possible, with qualified rows being passed up through the remaining nodes of the query plan.
Intermediate plan nodes can sort the data or join it to rows from other tables.
Each successive node performs some refinement on the rows received from below. The final result rows emerge from the top of the plan as requested by the query.
Modes for Showing Tree Diagrams
In the SQL Scratchpad window, you can show the tree diagram in one of two modes:
• Preview mode gives you a condensed version of the tree, where you can point to a particular node to see its detailed information.
• Normal mode displays the detailed information as part of the tree diagram.
Note: A query that has been modified to include views, integrities, and/or grants, is more involved. The QEP diagram for an involved query (as shown by set qep) can be very wide. On a printout, the diagram can even wrap so that similar levels in the tree appear on different levels. You can find it easier to read such a QEP if you cut and paste the diagram into the correct levels.
Concise QEP
SET QEP CONCISE displays the QEP in a format suitable for parsing by a program. The format is as follows:
<Query Plan No and timeout details>| {top node [{left child [{left child…}][{right child…}]};
The following example shows a QEP displayed in tree format and concise format.
Tree format
QUERY PLAN 1,1, no timeout, of main query
Hash Join(reltid,
reltidx)
Heap
Pages 1 Tups 91
D20 C41
/ \
Proj-rest Proj-rest
Heap Heap
Pages 1 Tups 177 Pages 9 Tups 1817
D2 C2 D18 C18
/ /
iirelation iiattribute
(r) (a)
cHashed(NU) cHashed(NU)
Pages 18 Tups 177 Pages 132 Tups 1817
Concise format
QUERY PLAN 1,1, no timeout, of main query | {Hash Join(reltid, reltidx) Heap Pages 1 Tups 91 D20 C41 {Proj-rest Heap Pages 1 Tups 177 D2 C2 {iirelation (r) cHashed(NU) Pages 18 Tups 177 }}{Proj-rest Heap Pages 9 Tups 1817 D18 C18 {iiattribute (a) cHashed(NU) Pages 132 Tups 1817 }}};
Last modified date: 01/30/2023