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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—523725-004
9-60
Examples of EXPLAIN
The DISPLAY_EXPLAIN command display for the prepared statement named
FINDEMP is identical to the DISPLAY_EXPLAIN command display shown under
DISPLAY_EXPLAIN Command on page 4-13.
•
Display the specified columns in the result table of the EXPLAIN function for the
same prepared statement FINDEMP:
SELECT SEQ_NUM, OPERATOR, OPERATOR_COST
FROM TABLE (EXPLAIN (NULL, 'FINDEMP'));
SEQ_NUM OPERATOR OPERATOR_COST
----------- ---------------------- --------------------
1 FILE_SCAN 1.6196700E-001
2 PARTITION_ACCESS 4.3732533E-003
3 ROOT 1.0392011E-006
--- 3 row(s) selected.
The preceding example displays only part of the result table of the EXPLAIN
function. It first uses the EXPLAIN function to generate the table and then selects
the desired columns.
•
Display the specified columns in the result table of the EXPLAIN function for the
same prepared statement but with two different plans. The first plan is the default
plan generated by the optimizer, and the second plan is forced by using the
CONTROL QUERY SHAPE statement.
This SET SHOWSHAPE command displays the plan generated by the optimizer:
SET SHOWSHAPE ON;
PREPARE FINDEMP1 FROM
SELECT last_name, first_name, deptnum,
employee.jobcode, jobdesc
FROM employee, job
WHERE deptnum = 3100 AND employee.jobcode = job.jobcode;
control query shape merge_join(sort(
partition_access(scan('EMPLOYEE',forward,
blocks_per_access 1 , mdam off))),
partition_access(scan('JOB', forward,
blocks_per_access 3 , mdam off)));
SELECT SEQ_NUM, OPERATOR, OPERATOR_COST, TOTAL_COST
FROM TABLE (EXPLAIN (NULL, 'FINDEMP1'));
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
3 SORT 1.9920971E-001 2.0409727E-001
4 FILE_SCAN 1.6560700E-001 1.6560700E-001
5 PARTITION_ACCESS 7.1685006E-003 1.6560700E-001
6 MERGE_JOIN 2.0821783E-003 2.1525979E-001
7 ROOT 2.7007004E-005 2.1528682E-001