Neoview Query Guide (R2.2)

1 Using Explain
A query execution plan represents the specific steps and actions that the Neoview database takes
to execute a query. Query plans show the exact access paths to database structures and provide
the estimated number of rows resulting from each step, as well as the relative cost. Query plans
are composed of a set of preexisting functions or methods called operators that implement a
particular task for a query. Operators and the operations they perform are discussed in Chapter 3
(page 31).
Why do you want to study query plans? You might want to verify something about how the
subsystem is working or find a solution to a specific problem. By reviewing the query plan, you
can determine if the performance is optimal.
You can view query plans by using a text-based method (EXPLAIN statement and EXPLAIN
function) or through a graphical user interface called Visual Query Planner (VQP).
The syntax and considerations for using the EXPLAIN statement and EXPLAIN function are
included in the Neoview SQL Reference Manual.
For information about using VQP to review plans, see Chapter 2 (page 19).
This section provides information about:
• “Difference Between EXPLAIN Statement and EXPLAIN Function”
• “Capturing Query Plans for Review” (page 13)
Difference Between EXPLAIN Statement and EXPLAIN Function
The EXPLAIN function is a table-valued stored function that displays the query plan in a
machine-readable format. It appears as one 4000 character line per node and is hard for humans
to read without reformatting. The EXPLAIN statement is derived from the EXPLAIN function
and improves the readability, content, and usefulness of the displayed query plans. Use the
EXPLAIN statement to quickly review query plans. Use the EXPLAIN function to capture query
plans for further analysis.
The EXPLAIN statement and EXPLAIN function can be executed from ODBC, JDBC, and Neoview
Script but you cannot use the EXPLAIN statement or EXPLAIN function in DB Admin.
Capturing Query Plans for Review
You can manage query plans with the EXPLAIN function by capturing query execution plans
that can be queried to search for plans that have changed. Cost and cardinality changes might
indicate a need for more careful review to see if the plan structure is still valid.
Capturing Query Plans
The results of the EXPLAIN function can be represented as a table for easier viewing. To capture
query plans, you must create and populate a new table with the query plans. This table provides
the backbone for the query plans you want to capture. An extended example of this process is
shown in “Capturing Query Plan Example” (page 14). Because the DDL might change in future
releases, your best practice is to regenerate the DDL as needed. This summary lists the steps to
capture query plans:
1. Use the INVOKE statement to show the list of columns in the Explain function. You then
can copy and paste the results from INVOKE following a CREATE TABLE tname clause to
create your own table with the same structure as the EXPLAIN table.
2. Use the CREATE TABLE statement to create the table for the captured query plans. Cut and
paste the columns from the INVOKE step into your CREATE TABLE statement and modify
it to add a clustering key.
Difference Between EXPLAIN Statement and EXPLAIN Function 13