SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-51
Nested Inner Join
The plan consists of two steps:
•
Plan step 1 includes a scan of the TENKTUP1 table, which is partitioned across
three disk volumes.
•
Plan step 2 includes a scan of the TENKTUP2 table, which is then inner joined with
the TENKTUP1 table.
Plan step 2 involves the following:
•
A hashing operation on the predicate A.TENPCT = B.TENPCT (instead of a sort
operation).
•
Performance of each operation in parallel:
°
Each ESP joins one of the partitions and performs a hash join with parallel
access
°
The current table is repartitioned three ways on the join column
°
An ESP is started to read the repartitioned data
Nested Inner Join
Example 6-33 on page 6-52 lists EXPLAIN output for a query that involves a scan of
two tables, TENKTUP1 (correlation name A) and TENKTUP2 (correlation name B).
The tables are joined according to this search condition: WHERE A.UNIQUE2 <10
AND B.UNIQUE1 = 3:
EXPLAIN SELECT * FROM TENKTUP1 A, TENKTUP2 B
WHERE A.UNIQUE2 < 10 AND B.UNIQUE1 = 3 ;
This EXPLAIN plan chooses parallel execution for an inner join query. The nested-join
method is chosen instead of a hashed, sort merge, or key-sequenced merge join
because the join predicate is “less than” (<) instead of equal.
Operation 2.1 : Hash
Requested : By the optimizer
Hash rows in the : Join composite excluding current table
Purpose : To hash its rows before the Join
Hash key columns : A.TENPCT
Hash cost : 126
Operation 2.2 : Hash
Requested : By the optimizer
Hash rows in the : Current table
Purpose : To hash its rows before the Join
Hash key columns : B.TENPCT
Hash cost : 631
Total cost : 7959
Example 6-32. EXPLAIN Plan for Hash Join (page 3 of 3)