SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Reviewing Query Execution Plans

HP NonStop SQL/MX Query Guide—523728-003

4-5

Displaying Selected Columns of the Execution Plan

This query execution plan example is a simple query using the PERSNL schema and

querying against the EMPLOYEE table:

PREPARE s1 FROM SELECT LAST_NAME, FIRST_NAME, SALARY

FROM EMPLOYEE ORDER BY SALARY;

In this query, the EXPLAIN function extracts these columns: SEQ_NUM, OPERATOR,

TOTAL_COST, and DESCRIPTION:

SELECT SEQ_NUM, OPERATOR, TOTAL_COST, DESCRIPTION

FROM TABLE (EXPLAIN (NULL, 'S1'));

This output is formatted for readability. The EXPLAIN function and DISPLAY_EXPLAIN

use machine-readable format for application program access.

1 FILE_SCAN 4.1293062E-002

scan_type: file_scan SAMDBCAT.PERSNL.EMPLOYEE

scan_direction: forward

key_type: simple

lock_mode: not specified

access_mode: not specified

columns_retrieved: 6

fast_scan: used

fast_replydata_move: used

key_columns: indexcol(SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM)

begin_key: (indexcol(SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM) = 0)

end_key: (indexcol(SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM) = 9999)

2 PARTITION_ACCESS 4.1293062E-002

buffer_size: 31000

record_length: 44

3 SORT 1.4935767E-001

4 ROOT 2.4738246E-001

statement_index: 0

statement: select last_name, first_name, salary from

samdbcat.persnl.employee

order by salary;

return select_list:

indexcol(SAMDBCAT.PERSNL.EMPLOYEE.LAST_NAME),

indexcol(SAMDBCAT.PERSNL.EMPLOYEE.FIRST_NAME),

indexcol(SAMDBCAT.PERSNL.EMPLOYEE.SALARY)

order_by: indexcol(SAMDBCAT.PERSNL.EMPLOYEE.SALARY)

IDLETIME An estimate of the number of seconds to wait for an event to happen.

The estimate includes the amount of time to open a table or start an

ESP process.

PROBES The number of times the operator will be executed. Usually, this value

is 1, but can be greater when you have, for example, an inner scan of

a nested-loop join.