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

MXCI Commands
HP NonStop SQL/MX Reference Manual—540440-003
4-14
Examples of DISPLAY_EXPLAIN
Purpose of the EXPLAIN Function Result
The EXPLAIN function result is an access plan. You can use the access plan to tune
queries and to help determine whether to add or drop indexes for a table in a
database.
For more information about access plans, see the SQL/MX Query Guide.
Examples of DISPLAY_EXPLAIN
•
Display all columns in the result of the EXPLAIN function for the specified
statement:
DISPLAY_EXPLAIN
SELECT * FROM persnl.employee
WHERE salary > 40000.00 AND jobcode = 450;
--- SQL command prepared.
MODULE_NAME
STATEMENT_NAME
PLAN_ID SEQ_NUM OPERATOR
LEFT_CHILD_SEQ_NUM RIGHT_CHILD_SEQ_NUM TNAME
CARDINALITY OPERATOR_COST TOTAL_COST
DETAIL_COST
DESCRIPTION
-------------------------------------------------
...
EXPL_NAME__
211864141076058828 1 FILE_SCAN
? ? SAMDBCAT.PERSNL.EMPLOYEE
2.4783301E+000 2.0646531E-002 2.0646531E-002 CPU_TIME:
0.000327 IO_TIME: 0.020647 MSG_TIME: 0 IDLETIME: 0 PROBES:
1
key_columns: indexcol(\SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM)
executor_predicates:
(indexcol(\SAMDBCAT.PERSNL.EMPLOYEE.SALARY) > 40000.00) and
(indexcol(\SAMDBCAT.PERSNL.EMPLOYEE.JOBCODE) = 450)
begin_key: (indexcol(\SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM) = 0)
end_key: (indexcol(\SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM) = 65535)
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
?
EXPL_NAME__
211864141076058828 2 PARTITION_ACCESS
1 ?
2.4783301E+000 2.0445892E-003 2.0646531E-002
CPU_TIME: 0.002372 IO_TIME: 0.020647 MSG_TIME: 0
IDLETIME: 0 PROBES: 1