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

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
(
VERSION_ID NUMERIC(4) NO DEFAULT NOT NULL
, VERSION_TIME TIMESTAMP(0) NO DEFAULT NOT NULL
, VERSION_DESC VARCHAR(32) NO DEFAULT NOT NULL
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
Capturing Query Plans for Review 13