SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-40
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.
{SCAN | FILE_SCAN | INDEX_SCAN} [(scan-option,...)]
specifies the options that you can use with SCAN, FILE_SCAN, or INDEX_SCAN,
as follows:
TABLE table
specifies a table or correlation name. table is a character string literal—for
example, 't1'.
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.