SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—523725-004
9-61
Examples of EXPLAIN
--- 7 row(s) selected.
The second plan is forced by this CONTROL QUERY SHAPE statement:
control query shape nested_join(
partition_access(scan),
partition_access(scan('JOB')));
PREPARE FINDEMP2 FROM
SELECT last_name, first_name, deptnum,
employee.jobcode, jobdesc
FROM employee, job
WHERE deptnum = 3100 AND employee.jobcode = job.jobcode;
SELECT SEQ_NUM, OPERATOR, OPERATOR_COST, TOTAL_COST
FROM TABLE (EXPLAIN (NULL, 'FINDEMP2'));
SEQ_NUM OPERATOR OPERATOR_COST TOTAL_COST
------- ---------------- --------------- ---------------
1 FILE_SCAN 1.6196700E-001 1.6196700E-001
2 PARTITION_ACCESS 4.4135637E-003 1.6196700E-001
4 FILE_SCAN_UNIQUE 2.0590099E-001 2.0590099E-001
5 PARTITION_ACCESS 4.5211268E-003 2.0590099E-001
6 NESTED_JOIN 1.7425649E-005 3.6786800E-001
7 ROOT 2.7007004E-005 3.6786800E-001
--- 6 row(s) selected.
You can compare the two result tables of the EXPLAIN function to determine which
plan to use for this query. The total cost of the ROOT node indicates the total cost
of the plan. Therefore, if you compare the two costs, the plan generated by the
optimizer is the better plan, as reported by the EXPLAIN function.