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

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-6
Parallel Plan Generation
A cost is associated with starting an ESP process. The optimizer balances this cost
against the performance gain resulting from the increased parallelism and chooses
ESP parallelism only if the gain exceeds the ESP start-up cost.
Parallel Plan Generation
The SQL/MX optimizer uses different methods to process operators, depending on the
general category of operator type, as described next.
Scan, Update, and Delete
DAM parallelism enables multiple PARTITION_ACCESS operators to access data
simultaneously by using asynchronous access. Data is consolidated through a
SPLIT_TOP node.
Join With Matching Partitions
If the optimizer finds that matching partitions exist in tables that are involved in a join, it
tries to join the tables by using the matching partitions algorithm. This type of plan is
also known as a Type1 join. Type1 joins can be forced by using the CONTROL
QUERY SHAPE statement. For more information, see Influencing Parallel Plans on
page 8-24.
In a matching partitions parallel join, the corresponding partitioning key columns of
both tables involved in the join must be linked through the join predicate. In addition,
for range partitioning, the first key values (that is, the partition boundaries) must match.
For hash partitioning, the number of partitions in each table must be identical, and the
data types of the corresponding partitioning key columns of both tables must be
identical.
Decoupled Keys
A decoupled table or index is one where the partitioning key is different from the
clustering key and it is not a prefix of the clustering key. Decoupled keys follow the
same rules as partitioning. If the clustering keys are compatible, the join is more
efficient
Range and Hash Partitioning
If the number of partitions match, but not the first key values, the optimizer can still use
the matching partitions algorithm while some form of repartitioning or logical
subpartitioning occurs to rectify the differing boundary values.
The optimizer has the ability to repartition one input, both inputs, or no inputs (where
input means outer and inner tables) before joining if the corresponding partitioning key
columns of both tables involved in the join are not linked through the join predicate or if
more partitions are needed to produce an optimal degree of parallelism.