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

Forcing Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
5-15
Forcing Parallel Plans
use a CUT for the children, or use an ESP_EXCHANGE above the children for any
necessary repartitioning.
This example shows forcing a Type1 join:
CONTROL QUERY SHAPE
ESP_EXCHANGE(
MERGE_JOIN(
EXCHANGE(SCAN('DEPT')),
EXCHANGE(SCAN('EMP')),
TYPE1)
);
The example uses the logical specification for the lower exchange operators to
enable the optimizer to choose parallel access (SPLIT_TOP_PA operator) or serial
access (PARTITION_ACCESS operator), as shown in Figure 5-6.
For more information about Type1 joins, see Parallelism on page 8-1.
•
Forcing a Type2 join
The join with parallel access to the inner table algorithm can be forced by using
Type2 as a part of the join specification. To force a Type2 nested join, the left child
must be partitioned. To force a Type2 hash join, the left child must be partitioned,
and you need to specify either CUT or ESP_EXCHANGE above the right child to
handle the broadcast replication.
The next statement forces a Type2 hash join, as shown in Figure 5-7 on
page 5-16.
CONTROL QUERY SHAPE
ESP_EXCHANGE(
HYBRID_HASH_JOIN (
EXCHANGE(SCAN('DEPT')),
ESP_EXCHANGE(EXCHANGE(SCAN('EMP'))),
TYPE2)
);
Figure 5-6. Logical Specification and Lower Exchange Operators
root
esp exchange
merge_join
exchange exchange
scan 'DEPT' scan 'EMP'
VST650.vsd