SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-38
CONTROL QUERY SHAPE Statement
join-shape (shape,shape [,{TYPE1 | TYPE2}]
[,num-of-esps]) | INDEXJOIN
specifies the join type and the number of ESPs that you can use with JOIN,
NESTED_JOIN, MERGE_JOIN, HASH_JOIN, HYBRID_HASH_JOIN, or
ORDERED_HASH_JOIN as follows:
TYPE1
specifies a parallel join with matching partitions.
TYPE2
specifies a join with parallel access to the inner table.
num-of-esps
specifies the number of ESPs
INDEXJOIN
specifies that the optimizer should use the index and base table to create a
scan, if possible.
INDEX_SCAN Replaces a SCAN node in operator tree.
JOIN Replaces a JOIN node in operator tree.
NESTED_JOIN Replaces a NESTED_JOIN in operator tree.
MERGE_JOIN Replaces a MERGE_JOIN in operator tree.
HASH_JOIN (cut,cut) or
HJ (cut,cut)
Replaces a HYBRID_HASH_JOIN or
ORDERED_HASH_JOIN in operator tree.
HYBRID_HASH_JOIN
(cut,cut) or HHJ (cut,cut)
Replaces a HYBRID_HASH_JOIN in operator tree.
ORDERED_HASH_JOIN
(cut,cut) or OHJ
(cut,cut)
Replaces an ORDERED_HASH_JOIN in operator tree.
Note. Regarding the ORDERED_HASH_JOIN and HYBRID_HASH_JOIN join shapes,
when ORDERED_HASH_JOIN is set, the optimizer forces the hash table (the inner/right
table in the join) into memory, so the order of the left/outer table is preserved. However, if
the inner table is very large (millions of rows), NonStop SQL/MX can run out of read-only
memory, so NonStop virtual memory would be used (flush pages in and out of disk, and so
on.) This virtual memory is slower than the HYBRID_HASH_JOIN mechanism. If you want
to guarantee the order of the left/outer table, you can use ORDERED_HASH_JOIN.
Consider the potential performance implication (depends on size of the right table, size of
memory, workload, and so on). You might want to explicitly use HYBRID_HASH_JOIN to
avoid the performance issue. However, if you specify HASH_JOIN (cut,cut), the optimizer
will present a plan with either HYBRID_HASH_JOIN or ORDERED_HASH_JOIN as
shown in the SHOWSHAPE of the resulting plan.