OPTCOMPIND

The OPTCOMPIND configuration parameter helps the 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.

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 default value), the optimizer selects a join method based on cost alone even though table scans can temporarily lock an entire table. For more information on 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.

SET ENVIRONMENT OPTCOMPIND

To set or modify the value of OPTCOMPIND within a session, use the SET ENVIRONMENT OPTCOMPIND command.

For example, you might want to change the value for different kinds of queries.

For a DSS query, you should set the value of OPTCOMPIND to 2 or 1, and you should be sure that the isolation level is not set to Repeatable Read. For an OLTP query, you could set the value to 0 or 1 with the isolation level not set to Repeatable Read.

The value that you enter using the SET ENVIRONMENT OPTCOMPIND command takes precedence over the default setting specified in the ONCONFIG file. The default OPTCOMPIND setting is restored when the current session terminates. No other user sessions are affected by SET ENVIRONMENT OPTCOMPIND statements that you execute.

For more details about the SET ENVIRONMENT SQL statement and the syntax for SET ENVIRONMENT OPTCOMPIND, see the IBM Informix: Guide to SQL Syntax