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

Parallelism

HP NonStop SQL/MX Query Guide—523728-003

8-10

Join With Parallel Access to the Inner Table

In joins where the inner and outer tables do not match and repartitioning is undesirable

or not possible, the optimizer can still achieve parallelism by using parallel access to

the inner table. This type of plan is also known as a Type2 join. Type2 joins can be

forced by using the CONTROL QUERY SHAPE statement. For more information, see

Influencing Parallel Plans on page 8-24.

In a parallel access join, each partition of the outer table is joined with the entire inner

table, which might or might not be partitioned. This strategy guarantees the correct

result regardless of how tables are partitioned and what the join predicates specify,

because any given row in an outer table partition will always be able to find its

matching rows in the inner table.

A nested join algorithm might be chosen by the optimizer based on the selectivity of

the predicate. If the selectivity is low and the number of probes required to the inner

table is small, the nested join algorithm might yield the optimal plan. However, if many

probes of the inner table are required (with a lot of concurrent access from the join

ESPs to the inner table), the nested join algorithm can cause a performance

degradation. This type of join is also called replicate no broadcast.

You can avoid performance degradation when the parallel access is combined with a

hash join, because the inner table is materialized in the memory of the join process.

Instead of randomly probing the inner table, each of the join processes receives a

complete copy of the inner table from a set of ESPs that broadcast the inner table

contents. When the join process receives a complete copy of the inner table, the join is

called replicate by broadcast.

The next figure shows a nested join with parallel access to the inner table.

Note. Replicate no broadcast means that no source ESPs exist. The target ESPs

independently read the data they need.

Note. Replicate by broadcast means that ESPs actually send multiple messages to each

recipient.