SQL/MX 2.x Reference Manual (H06.04+)

MXCI Commands

HP NonStop SQL/MX Reference Manual—540440-003

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-52.

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 the example following.

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 of 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-52.

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