Graphical User Interface

Graphical User Interface

Query Plan Viewer contains two windows: the Query Viewer and the Plan Viewer.

The Query Viewer displays the SQL query. Only one query plan can be viewed at a time but multiple plans can be opened concurrently. The Query Viewer also contains menu commands to open a query plan file, to navigate to the desired query plan when two or more plans are open concurrently, and to export a query plan.

The Query Viewer can be resized as necessary. It includes a vertical scrollbar for ease in viewing the SQL query.

The Plan Viewer contains a graphical representation of the query plan in a tree form with nodes that represent different components of the query.

The Plan Viewer can be resized as necessary. It includes vertical and horizontal scrollbars for ease in viewing the query plan tree in different sizes. It contains menu commands and keyboard shortcuts to resize the tree as necessary and to zoom in and out on the tree.

Different types of nodes appear in the Query Viewer depending on the query. Each node represents a step in the execution of a query. For example, nodes can represent selection from a base table, joining the results from two tables into a single result set, calculating an aggregate value, determining group break occurrences and combining group results into a single result set.

The following table summarizes the nodes.

Table 200

Plan Viewer Nodes

Represents data coming from a table in the database. The name displayed under the rectangle is the name of the table. If present, the name shown under the table indicates the index used to retrieve data from the table. An asterisk to the right of the index name indicates that the index contains unique values.

Represents a row selection operation. The word in parentheses can be either "Normal" or "Range:"

•

A Normal filter is applied after the row is returned from the downstream node.

•

A Range filter appears only directly above a base table. The Range filter causes a restricted record retrieval from the table based on an index value.

Performs a distinct operation. This node normally appears at the top or close to the top of the plan tree. Eliminates duplicates from the result set before returning rows from the query.

Detects group breaks based on a GROUP BY clause.

Works with Group Break node to assist in correctly accumulating aggregates for SELECT and HAVING clauses.

Performs a JOIN between two nodes. The value in parentheses indicates the type of JOIN performed:

•

"Outer" indicates left for right outer join without any indexes.

•

"OuterRange" indicates left or right outer join using an index.

•

"Normal" indicates a Cartesian join.

•

"Range" indicates an inner join using an index.

Performs aggregate value accumulation. This node is used for MIN, MAX, AVG, COUNT, SUM, and STDEV, and for these aggregates when the DISTINCT clause is used with them. The word inside the node indicates the type of aggregate being accumulated.

When an aggregate is accumulated in conjunction with a GROUP BY clause, the aggregate nodes appear between the group break and group nodes.

Handles accumulating aggregates when no GROUP BY clause is present.

Handles data retrieval from a single subquery of the main query. This node never appears when you view the "root" query plan, only when you view a subquery.

Handles creation of temporary tables and data retrieval from temporary tables. References to the base table under this node are changed to reference columns of the temporary table.

Processes the UNION and UNION ALL operations. Cycles through the underlying query execution plans to retrieve data for the UNION result set. The Plan Viewer displays unions with the first query as the root query, the second query as Subquery 1, and so forth.

Note: If you change the aspect ratio of the Plan Viewer while resizing it, the aspect ratio of the node symbols change accordingly. Consequently, they do not always look identical to the examples in the table.

If you double-click the following query plan nodes, a pop-up window appears, showing more detailed information:

•

•

•

•

Ordered temporary table

Double-clicking the other nodes provides no detailed information for them. When you mouse over a node with additional available details, the cursor changes to a hand.

The following table explains the type of detailed information displayed:

Detailed Information

•

•

Total rows in table

•

Estimated number of rows to be read

•

Range information. Range information is used only when the base table is on the right side of a JOIN and the retrieval of data from the table can be optimized through the use of an index. Range information includes:

•

Column or columns retrieved

•

Value used to initiate range retrieval (the value normally comes from another table and column) and the value to terminate retrieval

•

Initial operations to perform, such as greater than (GT), greater than or equal to (GE), less than (LT), and so forth

•

Method of comparison to determine when to stop (GT, GE, and so forth)

Filter (Normal)

Text representation of conditions used to evaluate row.

•

Information about index used

•

How filter is reducing set of rows returned

•

Type of first read from table (GT, GE, and so forth)

•

Condition that must evaluate to TRUE to stop reading more records (GT, GE, and so forth)

Type of subquery and optimizations being performed for subquery

Ordered temp table

•

List of columns included in the temporary table

•

Column indication of whether column is used to order rows of the temporary table (a key) or is a value to pass up the tree

The Plan Viewer also contains menu commands to view the plan at different zoom levels and to display subqueries, if any.