Neoview SQL Reference Manual (R2.2)

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 339).
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 104).
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. For example, this statement
returns the EXPLAIN result for all statements prepared within the current session:
SELECT * FROM TABLE (EXPLAIN (NULL,'%'))
For more information about the LIKE predicate, see the Neoview SQL Reference Manual.
This statement returns the EXPLAIN result for all statements prepared within the embedded
SQL module named MYCAT.MYSCH.MYPROG:
(module)SELECT * FROM TABLE (EXPLAIN ('MYCAT.MYSCH.MYPROG','%'))
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.
Result of the EXPLAIN Function
The result table of the EXPLAIN function describes the access plans for SELECT, INSERT,
DELETE, UPDATE, or CALL statements.
In this description of the result of the EXPLAIN function, an operator tree is a structure that
represents operators used in an access plan as nodes, with at most one parent node for each node
in the tree, and with only one root node.
EXPLAIN Function 339