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

MXCI Commands
HP NonStop SQL/MX Reference Manual—540440-003
4-16
Examples of DISPLAY_EXPLAIN
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
This use of the DISPLAY_EXPLAIN command first prepares the statement named
EXPL_NAME from the SELECT statement and then displays all of the columns of
the result of the EXPLAIN function applied to EXPL_NAME. For more information
about displaying only columns of particular interest, see EXPLAIN Function on
page 9-52.
•
This sequence provides the same result as the preceding example:
PREPARE FINDEMP FROM
SELECT * FROM persnl.employee
WHERE salary > 40000.00 AND jobcode =450;
DISPLAY_EXPLAIN FINDEMP;
This use of the DISPLAY_EXPLAIN command is similar to the SELECT statement:
SELECT * FROM TABLE (EXPLAIN (NULL, 'FINDEMP'));
•
This example shows the output of DISPLAY_EXPLAIN OPTIONS 'f':
PREPARE SQLQUERY FROM
SELECT l_orderkey, CAST(SUM(l_extendedprice*(1-l_discount))
AS NUMERIC(18,2)), o_orderdate, o_shippriority
FROM customer,orders,lineitem
WHERE c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1995-03-15'
AND l_shipdate > DATE '1995-03-15'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY 2 DESC,3 ASC;
DISPLAY_EXPLAIN OPTIONS 'f' SQLQUERY;
LC RC OP OPERATOR DESCRIPTION CARDINALITY
-- -- -- ------------------------- -------------------- ----------
12 . 13 root 1.23E+4
11 . 12 esp_exchange 1:2(range) 1.23E+4
10 . 11 sort 1.23E+4
9 . 10 hash_groupby 1.23E+4
2 8 9 hybrid_hash_join 3.26E+4
7 . 8 esp_exchange 2(range):2(range) 1.51E+4
4 6 7 merge_join 1.51E+4
5 . 6 partition_access 7.27E+4
. . 5 index_scan ORDERX1 (s) 7.27E+4
3 . 4 partition_access 3.11E+3
. . 3 file_scan CUSTOMER (s) 3.11E+3
1 . 2 partition_access 3.24E+5
. . 1 file_scan LINEITEM (s) 3.24E+5