SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-157
Examples of EXPLAIN
•
Use the EXPLAIN statement without the output options:
>>explain xx;
The following output is displayed:
------------------------------------------------------------------ PLAN
SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
PLAN_ID .................. 212122794829778687
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.23
STATEMENT ................ select *
from part
where p_partkey = (select max(ps_partkey) from
partsupp);
------------------------------------------------------------------ NODE
LISTING
ROOT ====================================== SEQ_NO 8 ONLY CHILD 7
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.2
EST_TOTAL_COST ........... 0.23
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
xn_access_mode ......... read_only
plan_version ....... 2,400
SCHEMA ................. CAT.SCH
select_list ............ CAT.SCH.PART.P_PARTKEY, CAT.SCH.PART.P_NAME,
CAT.SCH.PART.P_MFGR, CAT.SCH.PART.P_BRAND,
CAT.SCH.PART.P_TYPE, CAT.SCH.PART.P_SIZE,
CAT.SCH.PART.P_CONTAINER,
CAT.SCH.PART.P_RETAILPRICE,
CAT.SCH.PART.P_COMMENT
NESTED_JOIN =============================== SEQ_NO 7 CHILDREN 4, 6
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.04
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner
join_method ............ nested
PARTITION_ACCESS ========================== SEQ_NO 6 ONLY CHILD 5
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.02
DESCRIPTION
fragment_id ............ 3
parent_frag ............ 0
fragment_type .......... dp2
buffer_size ....... 31,000
record_length ........ 177
space_usage ............ 13:8:8:36
eid_space_computation on