Neoview Query Guide (R2.2)

3. Populate the table by preparing the query, and selecting and inserting it into the new table:

-- PREPARE THE QUERY

PREPARE q FROM

SELECT * FROM a;

-- INSERT THE PLAN INTO THE EXPLAIN TABLE

INSERT INTO EXPLAIN_TAB

SELECT 1, CURRENT_TIMESTAMP, 'R2.2 SUT22',

* FROM TABLE (EXPLAIN(NULL, 'Q'));

The prepared query in the SELECT statement must be uppercase. In the select list of the

insert-select statement, column values are added to support versioning for your table. The

first value, 1, corresponds to the VERSION_ID; the second value uses the

CURRENT_TIMESTAMP function to store the current timestamp; and the third value

provides descriptive text related to this version of the query. Another column displays the

module name:

-- SELECT VERSIONING INFORMATION

SELECT VERSION_ID, VERSION_TIME

SUBSTRING(VERSION_DESC, 1, 20),

SUBSTRING(MODULE_NAME, 1, 25)

FROM EXPLAIN_TAB WHERE VERSION_ID=1;

VERSION_ID VERSION_TIME (EXPR) (EXPR)

---------- --------------------- --------------- -------

1 2007-08-08 09:03:07 R2.2 SUT 22 ?

--- 4 row(s) selected.

Prepare your next query and INSERT-SELECT into your table.

Querying the Explain Table

This subsection provides a few sample queries that you can customize for retrieving information

from your Explain table. Replace EXPLAIN_TAB with your own table name.

To show each query and its total cardinality:

SELECT SUBSTRING(STATEMENT_NAME,1,20) AS QUERY,

CAST(CARDINALITY AS NUMERIC(14,4)) AS CARD

FROM EXPLAIN_TAB

WHERE OPERATOR = 'ROOT'

ORDER BY CARD DESC

FOR READ UNCOMMITTED ACCESS;

To show each query and its total cardinality and total cost:

SELECT * FROM (

SELECT

SUBSTRING(STATEMENT_NAME,1,20) AS QUERY,

CAST(CARDINALITY AS NUMERIC(18,4)) AS CARD,

CAST(TOTAL_COST AS NUMERIC(18,4)) AS TCOST

FROM EXPLAIN_TAB

WHERE OPERATOR = 'ROOT'

FOR READ UNCOMMITTED ACCESS

) AS T;

To show a distribution of costs for the queries:

16 Using Explain