NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-71
CONTROL QUERY Directive
HASH JOIN { OFF | ENABLE | SYSTEM }
specifies whether to allow SQL to use hash joins (joining algorithms based around
hash tables built largely in memory) when the optimizer expects such joins to
improve query performance.
The default for a program or SQLCI session is HASH JOIN SYSTEM. Specify
HASH JOIN ENABLE instead if you want to guarantee that the optimizer always
considers hash joins for your queries, even if Tandem changes the meaning of the
SYSTEM option.
You should normally leave HASH JOIN set to SYSTEM or ENABLE, because the
optimizer is designed to select a hash join only if the resulting plan improves the
performance of a query; prohibiting hash joins can degrade performance. You might
want to use HASH JOIN OFF when you know that contention for memory will be
unusually severe in the processor or processors in which your queries will run.
Note that the CONTROL TABLE directive includes a JOIN METHOD HASH
option you can use to force the optimizer to select a hash join in specific situations.
In such cases, a hash join forced by the CONTROL TABLE directive overrides any
CONTROL QUERY HASH JOIN OFF in effect at the same time.
INTERACTIVE ACCESS { ON | OFF }
specifies whether to optimize response time for returning only the first few rows
found (ON), or for returning all the rows found (OFF).
The default for a program or an SQLCI session is INTERACTIVE ACCESS OFF.
INTERACTIVE ACCESS ON is typically used when you want only the first few
rows in a result and you know an index is available on a column. It directs the
optimizer to use the index.
INTERACTIVE ACCESS ON might not change the optimal plan if a sort is
unavoidable. SQL might perform a sort if a query contains a UNION operator,
DISTINCT clause, aggregate function, ORDER BY clause, or GROUP BY clause.
(To avoid a sort, you can create an index on the appropriate columns, such as the
columns specified in the ORDER BY clause.)
MDAM { ON | OFF }
specifies whether or not to consider the Multidimensional Access Method (MDAM)
for the query.
MDAM ON is the default; it directs SQL to consider using MDAM for the query.
MDAM OFF turns MDAM off for all tables in the query plan.
OFF Prohibits the use of hash joins
ENABLE Allows the optimizer to use hash joins
SYSTEM Currently allows the optimizer to use hash joins (the same as ENABLE) but
is subject to change in future releases