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

Forcing Execution Plans

HP NonStop SQL/MX Query Guide—523728-003

5-16

Forcing Parallel Plans

For more information about Type2 joins, see Parallelism on page 8-1.

•

Deferring to the optimizer to choose exchange or sort operators

In the preceding forced join examples, you needed to specify the choice of

exchange and sort operators as part of the CONTROL QUERY SHAPE statement.

To eliminate this step and simplify the process of writing a valid CONTROL

QUERY SHAPE statement, you can use one of the options listed below with the

CONTROL QUERY SHAPE statement. Using these options enables you to focus

on join orders, join types, and other plan types while deferring the choice of

exchange or sort operators to the optimizer:

IMPLICIT EXCHANGE enables the optimizer to add exchange nodes at any

location necessary to make the CONTROL QUERY STATEMENT valid. The

optimizer chooses the optimal placement of the exchange nodes. You can still

explicitly add in exchange nodes and the compiler must add them.

IMPLICIT SORT enables the optimizer to add sort nodes at any location

necessary to make the CONTROL QUERY STATEMENT valid. The optimizer

chooses the optimal placement of the sort nodes.You can still explicitly add in

sort nodes and the compiler must add them.

IMPLICIT EXCHANGE_AND_SORT enables the optimizer to add both

exchange and sort nodes at any location necessary to make the CONTROL

QUERY STATEMENT valid. The optimizer chooses the optimal placement of

the exchange and sort nodes. You can still explicitly add in enforcers and the

compiler must add them.

Figure 5-7. Type2 Hash Join

root

hybrid_hash_join

scan 'DEPT' exchange

scan 'EMP'

exchange

esp_exchange

VST651.vsd