Query plan shape

The position of each operator in the tree determines its order of execution. Execution starts down the left-most branch of the tree and proceeds to the right. To illustrate execution, this section steps through the execution of the query plan for the example in the previous section. Figure 2-1 shows a graphical representation of the query plan.

Figure 2-1: Query plan

Graphic showing the query processing plan with EmitOP as the top operator and various children operators below.

To generate a result row, the EMIT operator calls for a row from its child, the MERGE JOIN operator (1), which calls for a row from its left child, the SCAN operator for salesdetailind. When EMIT receives a row from its left child, MERGE JOIN operator (1) calls for a row from its right child, MERGE JOIN operator (2). MERGE JOIN operator (2) calls for a row from its left child, the SCAN operator for sales.

When it receives a row from its left child, MERGE JOIN operator (2) calls for a row from its right child, the SCAN operator. The SCAN operator is a data-blocking operator. That is, it needs all of its input rows before it can sort them, so the SORT operator keeps calling for rows from its child, the SCAN operator for stores, until all rows have been returned. Then the SORT operator sorts the rows and passes the first row to the MERGE JOIN operator (2).

The MERGE JOIN operator (2) keeps calling for rows from either the left or right child operators until it gets two rows that match on the joining keys. The matching row is then passed up to MERGE JOIN operator (1). MERGE JOIN operator (1) also calls for rows from its child operators until a match is found, which is then passed up to the EMIT operator to be returned to the client. In effect, the operators are processed using a left-deep postfix recursive strategy.

Figure 2-2 shows a graphical representation of an alternate query plan for the same example query. This query plan contains all of the same operators, but the shape of the tree is different.

Figure 2-2: Alternate query plan

Graphic showing an alternative query plan with EmitOP as the top operator and various children operators below.

The showplan output corresponding to the query plan in Figure 2-2 is:

QUERY PLAN FOR STATEMENT 1 (at line 1).

6 operator(s) under root

The type of query is SELECT.

ROOT:EMIT Operator

    |MERGE JOIN Operator (Join Type: Inner Join)
    | Using Worktable3 for internal storage.
    |  Key Count: 1
    |  Key Ordering: ASC
    |
    |   |MERGE JOIN Operator (Join Type: Inner Join)
    |   | Using Worktable2 for internal storage.
    |   |  Key Count: 1
    |   |  Key Ordering: ASC
    |   |
    |   |   |SCAN Operator
    |   |   |  FROM TABLE
    |   |   |  sales
    |   |   |  Table Scan.
    |   |   |  Forward Scan.
    |   |   |  Positioning at start of table.
    |   |   |  Using I/O Size 2 Kbytes for data pages.
    |   |   |  With LRU Buffer Replacement Strategy for data pages.
    |   |
    |   |   |SORT Operator
    |   |   | Using Worktable1 for internal storage.
    |   |   |
    |   |   |   |SCAN Operator
    |   |   |   |  FROM TABLE
    |   |   |   |  stores
    |   |   |   |  Table Scan.
    |   |   |   |  Forward Scan.
    |   |   |   |  Positioning at start of table.
    |   |   |   |  Using I/O Size 2 Kbytes for data pages.
    |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
    |
    |   |SCAN Operator
    |   |  FROM TABLE
    |   |  salesdetail
    |   |  Index : salesdetailind
    |   |  Forward Scan.
    |   |  Positioning at index start.
    |   |  Index contains all needed columns. Base table will not be read.
    |   |  Using I/O Size 2 Kbytes for index leaf pages.
    |   |  With LRU Buffer Replacement Strategy for index leaf pages.

The showplan output conveys the shape of the query plan by using indentation and the pipe (“|”) symbol to indicate which operators are under which and which ones are on the same or different branches of the tree. There are two rules to interpreting the tree shape:

Using these rules, the shape of the query plan in Figure 2-2 can be derived from the previous showplan output with the following steps:

  1. The ROOT or EMIT operator is at the top of the query plan tree.

  2. MERGE JOIN operator (1) is the left child of the ROOT. The vertical line that starts at MERGE JOIN operator (1) travels down the length of the entire output, so all of the other operators are below MERGE JOIN operator (1) and on the same branch.

  3. The left child operator of the MERGE JOIN operator (1) is MERGE JOIN operator (2).

  4. The vertical line that starts at MERGE JOIN operator (2) travels down past a SCAN, a SORT, and another SCAN operator before it ends. These operators are all nested as a subbranch under MERGE JOIN operator (2).

  5. The first SCAN under MERGE JOIN operator (2) is its left child, the SCAN of the sales table.

  6. The SORT operator is the right child of MERGE JOIN operator (2) and the SCAN of the stores table is the only child of the SORT operator.

  7. Below the output for the SCAN of the stores table, several vertical lines end. This indicates that a branch of the tree has ended.

  8. The next output is for the SCAN of the salesdetail table. It has the same indentation as MERGE JOIN operator (2), indicating that it is on the same level. In fact, this SCAN is the right child of MERGE JOIN operator (1).

NoteMost operators are either unary or binary. That is, they have either a single child operator or two child operators directly beneath. Operators that have more than two child operators are called “nary”. Operators that have no children are leaf operators in the tree and are termed “nullary.”

Another way to get a graphical representation of the query plan is to use the command set statistics plancost on. See Adaptive Server Reference Manual: Commands for more information. This command is used to compare the estimated and actual costs in a query plan. It prints its output as a semigraphical tree representing the query plan tree. It is a very useful tool for diagnosing query performance problems.