Neoview SQL Reference Manual (R2.3)

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 350).
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 106).
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.
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.
350 SQL Functions and Expressions