Access plan graphs

An access plan graph graphically displays the access plan for any statement that the workflow assistant can gather EXPLAIN data for.

When DB2® processes an SQL statement, the DB2 optimizer generates several alternative plans for accessing the requested data. The optimizer estimates the execution cost of each plan and executes the plan that has the lowest cost. This plan is called the access plan.

An access plan graph consists of nodes and lines that connect those nodes. The nodes represent data sources, operators, SQL statements, and query blocks. Nodes can have only one parent node, but they can have unlimited child nodes. The arrows on the edges indicate the direction of the flow. Typically, a table node is at the bottom of the graph, and the access plan proceeds upward from there.

Some operations in the access plan, such as nested loop joins or index scans, are represented in the graph by groups of nodes, which are called constructs. Many of these constructs have a defining node that indicates the operation. For example, the HBJOIN node indicates that a hybrid join operation is taking place, but the entire hybrid join is represented in the graph by a group of nodes. This group of nodes represents all the other data sources and operations that are involved in the hybrid join.

An SQL statement can consist of several subqueries, which are represented in the access plan diagram by query blocks. The subquery can be a SELECT, INSERT, UPDATE, or DELETE statement. A subquery can contain other subqueries in the FROM clause, the WHERE clause, or a subselect of a UNION or UNION ALL clause. A subquery within another subquery is called a child subquery. A subquery that contains another subquery is called a parent subquery. This parent-child relationship can be represented by a tree hierarchy.

If a subquery references at least one column of its parent subquery or of any parent subqueries that are higher up in the tree hierarchy, the subquery is a correlated subquery; otherwise it is a non-correlated subquery. A non-correlated subquery can run at the same time as the highest parent subquery that is also non-correlated. This highest parent subquery is called the "do-at-open parent subquery" in terms of its relationship to the non-correlated subquery. The execution of a correlated subquery is bound to the execution of its parent subquery. Such relationships between the relative executions of parents and children can be represented by separate tree hierarchies in the access plan graph.

Non-correlated subquery
The query block node is connected to the right side of the query block node for the highest parent subquery that is also non-correlated.
Correlated subquery
The query block node is connected to the part within its parent subquery where the correlated subquery is executed.

Feedback