SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

MXCI Commands

HP NonStop SQL/MX Reference Manual—523725-004

4-13

DISPLAY_EXPLAIN Command

Considerations for DISPLAY_EXPLAIN

Examples of DISPLAY_EXPLAIN

The DISPLAY_EXPLAIN command generates and displays the result of the EXPLAIN

function, describing an access plan for a SELECT, INSERT, DELETE, UPDATE, or

CALL statement. For a description of the result table of the EXPLAIN function, see

EXPLAIN Function on page 9-55.

You can use DISPLAY_EXPLAIN only within an MXCI session.

[OPTIONS 'f']

is an option that formats machine-readable DISPLAY_EXPLAIN output into

columns listing LC, RC, OP, OPERATOR, DESCRIPTION, CARDINALITY, and

OPT. OPT contains additional information for some operators indicating special

query optimizations that were applied. See this example:

It is valid only with statement-name. 'f' must be lowercase and must be

enclosed in single quotes.

statement

is an SQL DML statement. If statement is specified, the DISPLAY_EXPLAIN

command first prepares the statement.

statement-name

is the name of a prepared SQL DML statement—that is, the statement name used

in the PREPARE statement. The statement name in a PREPARE statement (and

therefore in a DISPLAY_EXPLAIN command) is an SQL identifier. The statement

name is not case-sensitive.

Considerations for DISPLAY_EXPLAIN

Using EXPLAIN and DISPLAY_EXPLAIN

For a specific DML statement, the result of the EXPLAIN function (or the access plan

for the statement) can be generated either by using the EXPLAIN function or the

DISPLAY_EXPLAIN command. Use the EXPLAIN function only for prepared

statements.

The DISPLAY_EXPLAIN command displays all the columns of the result table of the

EXPLAIN function in machine-readable format. If you want to display only some of the

columns, you must use the EXPLAIN function to return the intermediate result table

that you then query with a SELECT statement. See EXPLAIN Function on page 9-55.

DISPLAY_EXPLAIN {statement | [OPTIONS 'f'] statement-name}