SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Forcing Execution Plans

HP NonStop SQL/MX Query Guide—523728-003

5-3

Reviewing the Optimized Plan

The next example shows the EXPLAIN output for the optimized sample query. While

this output simply shows the operators and sequence numbers, you will also want to

select the costing columns to review the estimated costs of each operation.

>>SET SCHEMA samdbcat.persnl;

>>PREPARE s1 FROM SELECT employee.last_name, employee.first_name,

>+dept.manager, employee.deptnum, job.jobcode

>+FROM dept, employee, job

>+WHERE dept.deptnum=3100 AND employee.deptnum=3100

>+AND job.jobcode=300;

--- SQL command prepared.

>>SELECT seq_num, operator, left_child_seq_num, right_child_seq_num

>+FROM table (EXPLAIN(NULL, 'S1'));

SEQ_NUM OPERATOR LEFT_CHILD_SEQ_NUM RIGHT_CHILD_SEQ_NUM

-------- ------------------ ------------------ -------------------

5 FILE_SCAN ? ?

6 PARTITION_ACCESS 5 ?

3 FILE_SCAN_UNIQUE ? ?

4 PARTITION_ACCESS 3 ?

7 HYBRID_HASH_JOIN 6 4

1 FILE_SCAN_UNIQUE ? ?

2 PARTITION_ACCESS 1 ?

8 HYBRID_HASH_JOIN 7 2

9 ROOT 8 ?

--- 9 row(s) selected.

The output shows that the query plan consists of three scan operators in DAM with

exchange (PARTITION_ACCESS) operators that are responsible for the

communication between the application process and DAM, two hybrid hash join

operators, and a root node.

When you review the output, look at the sequence numbers for the node and the left

and right child sequence numbers. Start reading from the root node. The output shows

that the ROOT node has one child, a HYBRID_HASH_JOIN. This node has two

children, another HYBRID_HASH_JOIN and a PARTITION_ACCESS with a file scan

operation. The second HYBRID_HASH_JOIN also has two children nodes, each a

PARTITION_ACCESS node with file scan operations. For more information about the

operators, see Section 7, Operators and Operator Groups.