Neoview SQL Reference Manual (R2.5)

EXPLAIN Function
• “Considerations for EXPLAIN Function”
• “Examples of EXPLAIN Function”
The EXPLAIN function is a table-valued stored function that generates a result table describing
an access plan for a SELECT, INSERT, DELETE, UPDATE, or CALL statement. See “Result of
the EXPLAIN Function” (page 402).
The EXPLAIN function can be specified as a table reference (table) in the FROM clause of a
SELECT statement if it is preceded by the keyword TABLE and surrounded by parentheses.
For information on the EXPLAIN statement, see “EXPLAIN Statement” (page 124).
EXPLAIN (module,'statement-pattern')
module is:
'module-name' | NULL
'module-name'
Reserved for future use.
The module name is enclosed in single quotes and is case-sensitive. If a module name is
uppercase, the value you specify within single quotes must be uppercase. For example:
'MYCAT.MYSCH.MYPROG'
NULL
explains statements prepared in the session.
'statement-pattern'
A statement pattern is enclosed in single quotes and is case-sensitive. The statement name
must be in uppercase, unless you delimit the statement name in a PREPARE statement.
Considerations for EXPLAIN Function
Using a Statement Pattern
Using a statement pattern is analogous to using a LIKE pattern. You can use the LIKE pattern
in the following ways:
select * from table (explain(NULL,'S%'));
select * from table (explain(NULL,'S1'));
select * from table (explain(NULL,'%1'));
However, you cannot use the LIKE pattern in this way:
SELECT * FROM TABLE (EXPLAIN (NULL, '%'))
This statement returns the EXPLAIN result for all prepared statements whose names begin with
the uppercase letter 'S':
SELECT * FROM TABLE (EXPLAIN (NULL,'S%'))
If the statement pattern does not find any matching statement names, no rows are returned as
the result of the SELECT statement.
Obtaining an EXPLAIN Plan While Queries Are Running
Neoview SQL provides the ability to capture an EXPLAIN plan for a query at any time while
the query is running with the QID option. By default, this behavior is disabled for a Neoview
session. To enable this feature, please contact your HP Support representative for assistance.
NOTE: Enable this feature before you start preparing and executing queries.
After this feature is enabled, use the following syntax in an EXPLAIN function to get the query
execution plan of a running query:
SELECT * FROM TABLE (EXPLAIN(NULL, 'QID=qid'))
EXPLAIN Function 401