When the query optimizer evaluates a query (such as the SQL statements SELECT, INSERT, UPDATE, DELETE, and CREATE TABLE...AS), it generates a QEP showing how the query is executed. Once the QEP has been generated, it can be used one or more times to execute the same query. Because there are often many ways to optimize a given query, choosing the best QEP can have a significant impact on performance.

You can display a diagram or graph of the QEP selected, which can be used to gain insight into how queries are handled by the query optimizer. Knowing how to read and analyze QEPs can allow you to detect, and often avoid, hidden performance problems. After examining a QEP you can, for example, decide that you need to optimize your database to provide the optimizer with better statistics, as described in Database Statistics.

Examining QEPs can help you understand what is involved in executing complex queries in single-user situations.

The information that can appear on a QEP is as follows:

In general, it is a good idea to test run a query (that is, view the QEP).

In Director, in the Query tab, click Options, Execution, Advanced, View Query Plan. When you execute the query, the QEP is displayed in the Trace tab.

In VDBA, if you open the SQL Scratchpad window and click Execute QEP, you automatically see the query execution plan in a graphical form.

From a terminal monitor or embedded SQL, you can see the QEP by using the SET QEP statement. On the SET statement, the [NO]OPTIMIZEONLY, [NO]QEP, and JOINOP [NO]TIMEOUT clauses pertain to QEPs. The QEP is displayed in text-only format when using SQL.

To control whether QEPs are generated using an operating system environment variable, issue the following commands:

C shell:

Bourne shell:

In a terminal monitor, a QEP is displayed as a tree, where each node has one or two children:

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:

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.

In the SQL Scratchpad window, you can show the tree diagram in one of two modes:

SET QEP CONCISE displays the QEP in a format suitable for parsing by a program. The format is as follows:

The following example shows a QEP displayed in tree format and concise format.

In Director and in VDBA, QEP diagrams appear in the query information pane as a graph.

For a detailed description of each element in the graph, see the online help.

Each node in a QEP has detailed information associated with it, depending on the type of node.

The types of nodes are as follows:

Many types of nodes can also be shown as sort nodes. A sorting node causes the data to be sorted as it is returned. Any node other than an orig node can appear with a sort indication. A query with a SORT clause has a sort node as the topmost node in the QEP. This type of node displays:

For a description of each of these parts of the display, see Information on a QEP.

Sort nodes make use of a sort buffer and so consume primarily CPU resources, requiring disk I/O only when the volume of data to be sorted cannot be accommodated in the sort buffer. The heapsort algorithm is used; it is very fast on both unordered data and data which is nearly sorted.

Types of non-join nodes are as follows:

Orig nodes are nodes with no children. When reading a QEP, you should first find the orig nodes of the tree. Orig nodes are the most common type of QEP node.

Orig nodes describe a base table or secondary index being accessed from the query. This type of node displays the following:

For a description of each of these parts of the display, see Information on a QEP.

A projection-restriction (proj-rest) node describes the result of a projection and/or WHERE clause restriction applied to a subsidiary node. It defines how a subsidiary node is to key into the table and what qualification to use on the table. This type of node displays the following:

For a description of each of these parts of the display, see Information on a QEP.

Proj-rest nodes are used to remove data irrelevant to the query from a table, so that the minimum amount of data is carried around during the execution of the query. Therefore, only those columns referenced in the target list and WHERE clause for a table are projected, and only those rows that satisfy the WHERE clause restrictions are retained for use higher in the plan.

All you see is the amount of disk I/O required to read the appropriate rows from the node below, and that amount depends on what storage structures were used and the number of pages accessed.

Exchange nodes appear in parallel query plans. An exchange node results in one or more threads being spawned to execute the plan fragment beneath the node. It allows different portions of a complex query plan to execute concurrently, reducing the overall elapsed time taken to execute the query. This type of node displays:

Here are QEP examples that illustrate non-join nodes. The Sample Tables section describes the tables and indexes used in these examples.

In these examples, the following two tables are used:

This is an example of a simple primary key lookup. The QEP is shown below for the following SQL query:

Reading this QEP diagram from bottom to top, Hashed(col1) means the row is being read through the index to return only those rows for which “col1 = 1234,” as opposed to Hashed(NU) where NU indicates that the key structure cannot be used and all rows are returned. The projection-restriction node selected the rows matching the where constraint and removed superfluous columns. The final sort node reflects the SORT clause on the query.

The following is an example of a select on a non-keyed field. The QEP is shown below for the following SQL query:

In this example the Hashed(NU) implies that the table had to be scanned (that is, all 70 pages had to be visited). Without optimization statistics, the query optimizer uses a best guess approach (1% for equalities and 10% for non-equalities).

The query optimizer considers disk read-ahead or group reads when performing scans of tables—although 70 pages of data have to be read to scan arel, the estimated disk I/O value is only nine reads (D9) due to this effect. The query optimizer assumes a typical read-ahead of eight pages when performing scans, so here 70/8 reads generates an estimate of nine disk operations.

There is an inner and an outer tree beneath every join node, which function similarly to an inner and outer program loop. By convention, the left-hand tree is called the outer tree, and the right-hand tree is called the inner tree.

There are several types of join nodes, described individually below, but the joining method is the same: for each row from the outer tree, there is a join to all the rows that can possibly qualify from the inner tree. The next row from the outer tree is processed, and so on.

Any join node can have outer join information if an outer join is present.

The Cartesian product, or cart-prod, strictly follows the unoptimized join model, with each row in the outer node compared to all rows from the inner node. This does not mean that all rows are read, only that all rows that satisfy the conditions of the query are compared.

A typical abbreviated example of a QEP diagram involving a cart-prod is shown below:

This node is displayed with the following information on a QEP diagram:

The cart-prod is most frequently observed in disjoint queries (that is, when use of correlation variables and table names are mixed in the query). However, the following cases can also generate cart-prods, without adversely affecting performance:

Cart-prods are sometimes associated with substantial estimates for disk I/O usage and affect performance adversely.

This example shows a QEP diagram with a cart-prod join node resulting from the following simple disjoint query:

The full sort merge (FSM) is a more optimal join: it typically joins the inner and outer subtrees with many fewer comparisons than the cart-prod requires. This is done by assuring that both subtrees are sorted in the order of the join columns. If one or the other is not already sorted (for example, by being read from a B-tree index constructed on the join columns), the query plan can include a sort to put the rows in the correct order. This allows the rows of the outer subtree to be joined to the matching rows of the inner subtree with one pass over each. The inner subtree is not scanned multiple times, as with the cart-prod join.

A typical abbreviated example of a QEP diagram involving an FSM is shown below:

This node is displayed with the following information on a QEP diagram:

The FSM is most common when a “bulk” join takes place with no row restrictions on either table involved in the join, as with a SELECT statement of the following form:

This example shows a QEP diagram with an FSM join node resulting from such a bulk join:

The partial sort merge (PSM) is a cross between a full sort merge and a cart-prod. The inner tree must be in sorted order. The outer tree can be sorted or partially sorted. The outer tree in PSM scenarios can always be derived from an ISAM table. Comparisons proceed as for the full sort merge until an outer value is found to be out of order. At that point the inner loop is restarted. Because ISAM tables are reasonably well ordered (depending on how many rows have been added because the last reorganization), the number of inner loop restarts is typically small.

A typical abbreviated example of a QEP diagram involving a PSM is shown below:

This node is displayed with the following information on a QEP diagram:

The following example shows a QEP diagram with a PSM join:

The hash is an optimized join that replaces the FSM join when one or both subtrees must be sorted. It functions by loading the rows of one subtree into a memory resident hash table, keyed on the values of the join columns. The rows of the other subtree are hashed on their join key values into the hash table, allowing very efficient matching of joined rows. By avoiding the sort(s) of the FSM join, the hash join can be much more efficient.

A typical abbreviated example of a QEP diagram involving a hash join is shown below:

This node is displayed with the following information on a QEP diagram:

Like the FSM join, the hash join is most common when a bulk join takes place with no row restrictions on either table involved in the join, as with a SELECT statement of the following form:

This example shows a QEP diagram with hash join node resulting from such a bulk join:

In key and tid lookup joins, the outer and inner data set is not static. For each outer row, the join selects values and forms a key to search in the inner join. A key lookup join uses keyed access through the structure of the inner table or index, and a tid lookup join uses the tuple identifier (tid) value.

A typical abbreviated example of a QEP diagram involving this type of join is shown below:

This node is displayed with the following information on a QEP diagram:

This case is seen most frequently where the outer subtree proj-rest returns few rows, so it is faster to do a keyed lookup (on an ISAM, hash, or B-tree table) than any of the sort merge operations.

The following example shows a QEP diagram with a key lookup join:

In the next example of a tid lookup join, access to the base table is through the secondary index, and proj-rest collects tids for sorting. The tids are used for a direct tid lookup in the base table. Therefore, the storage structure of the base table is NU:

The subquery join is specific to SQL because SQL allows subselects as part of a query. These nodes join rows from a query to matching rows of a contained subselect, thus allowing the subselect restrictions on the query to be evaluated.

A typical abbreviated example of a QEP diagram involving a subquery join is shown below:

In this diagram, Tn identifies the QEP constructed to evaluate the subselect.

This node is displayed with the following information on a QEP diagram:

The following example shows a QEP diagram with a subquery join:

In the QEP pane of the SQL Scratchpad window in VDBA, these two QEPs are shown in separate tabs.

Subquery joins are reasonably expensive because the subselect must be executed once for each row of the outer subtree. The query optimizer contains logic to flatten various types of subselects into normal joins with the containing query. This allows more optimal joins to be used to evaluate the subselect.

As an example of the subselect flattening enhancement features of the query optimizer, consider the following subselect:

Instead of two scans of table r, the query optimizer has eliminated a scan of table r by evaluating the aggregate at an interior QEP node. The QEP appears similar to the previous example:

The query optimizer can generate multiple QEPs if the query includes any of the following objects:

As an example of multiple QEPs, consider the processing of a view on a union. The following statement creates the view:

There are two selects, designated #1 and #2 in their respective QEPs below. Now consider the query optimizer action in evaluating the following query:

This generates three QEPs, which are shown in order in the example QEP diagrams that follow:

The previous series of QEPs on the different classes of joins involved only two tables. More complex QEPs involving joins with three or more tables can be read as a sequence of two-table joins that have already been described, with the query optimizer deciding what is the optimal join sequence. The key to understanding these complex QEPs is recognizing the join sequences and the types of joins being implemented.