Optimizing access methods
The OPTCOMPIND configuration parameter helps the query optimizer choose an appropriate access method for your application. When the optimizer examines join plans, OPTCOMPIND indicates the preferred method for performing the join operation for an ordered pair of tables.
About this task
If OPTCOMPIND is equal to 0, the optimizer
gives preference to an existing index (nested-loop join) even when
a table scan might be faster. If OPTCOMPIND is set to 1 and
the isolation level for a given query is set to Repeatable Read, the
optimizer uses nested-loop joins.
When OPTCOMPIND is equal
to 2, the optimizer selects a join method based on
cost alone even though table scans can temporarily lock an entire
table. For more information about OPTCOMPIND and the different join
methods, see Effect of OPTCOMPIND on the query plan.
To set the value for OPTCOMPIND for specific applications or user sessions, set the OPTCOMPIND environment variable for those sessions. Values for this environment variable have the same range and semantics as for the configuration parameter.