Neoview Query Guide (R2.3, R2.4, R2.5)

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”. 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.
3. Populate the table. Populating the table is a two-step process:
a. Prepare the query
b. Select and insert into the new table
Historical Explain Database
You might want to maintain query plans over a period of time or generate the same query plans
using different settings. You can capture these plans by using the same basic directions described
in “Capturing Query Plans”, but by adding some additional columns that describe the unique
properties of the query plans. For example, you might want to add a VERSION_ID column that
represents the version of the plan for the same query or a VERSION_TIME column that provides
the time when the plan was generated and inserted.
12 Using Explain