Neoview Query Guide (R2.2)

3. Populate the table. Populating the table is a two-step process:
a. Prepare the query
b. Select and insert into the new table
NOTE: You might be curious about whether you can capture explain plans in VQP.
You cannot use VQP to capture multiple explain plans or load the plans back into VQP.
Also, you cannot load multiple plans into a single instance of VQP.
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” (page 13), 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.
Capturing Query Plan Example
1. Generate the list of columns in the Explain function with the INVOKE statement:
INVOKE TABLE (EXPLAIN (NULL, NULL));
-- Definition of table SUPER.SUPER.EXPLAIN__
-- Definition current Mon Dec 11 19:53:04 2006
(
MODULE_NAME CHAR(60) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT
, STATEMENT_NAME CHAR(6) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT NOT NULL
, PLAN_ID LARGEINT NO DEFAULT
, SEQ_NUM INT NO DEFAULT
, OPERATOR CHAR(30) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT
, LEFT_CHILD_SEQ_NUM INT NO DEFAULT
, RIGHT_CHILD_SEQ_NUM INT NO DEFAULT
, TNAME CHAR(60) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT
, CARDINALITY FLOAT(22) NO DEFAULT
, OPERATOR_COST FLOAT(22) NO DEFAULT
, TOTAL_COST FLOAT(22) NO DEFAULT
, DETAIL_COST CHAR(200) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT
, DESCRIPTION CHAR(3000) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT
)
--- SQL operation complete.
2. Create the table by using the columns from the Explain function that you generated with
the INVOKE TABLE command. You need to add a clustering key that serves as the primary
key for the table. Each column of the clustering key must include the NOT NULL attribute
in CREATE TABLE. In the example, STATEMENT_NAME is designated as NOT NULL. As
part of the clustering key, in the STORE BY clause, notice that a SYSKEY is added. The
SYSKEY provides a unique identifier for each row. The NUMBER OF PARTITIONS clause
controls the number of partitions in the table. In this case, 1 is specified.
This DDL also includes version columns (VERSION_ID, VERSION_TIME, and
VERSION_DESC) that you can use for historical purposes. If you are creating an Explain
table for other purposes, simply eliminate those columns.
CREATE TABLE EXPLAIN_TAB
14 Using Explain