OPTCOMPIND session environment option

Use the OPTCOMPIND session environment option of the SET ENVIRONMENT statement to specify methods for the query optimizer to choose in join queries and MERGE statements of the currently executing routine.

The SET ENVIRONMENT OPTCOMPIND statement of SQL supports the following syntax:

OPTCOMPIND environment option

SET ENVIRONMENT OPTCOMPIND { '0' | '1' | '2' | DEFAULT }

Usage

The OPTCOMPIND environment option can improve the performance of databases that are used for both decision support and online transaction processing. Use this option to specify join methods for the query optimizer to use in queries of the same routine.

'0' or "0": The query optimizer uses a nested-loop join where possible, rather than a sort-merge join or a hash join
'1' or "1": If the transaction isolation level is Repeatable Read, the optimizer behaves as in setting '0', as described above; for any other isolation level, it behaves like setting '2', as described next.
'2' or "2": The query optimizer does not necessarily prefer nested-loop joins, but bases its decision entirely on the estimated cost, regardless of the transaction isolation mode.
DEFAULT: This keyword restores the system default value, as described in the OPTCOMPIND topic of the Informix® Guide to SQL: Reference

Any numeric setting of the SET ENVIRONMENT OPTCOMPIND statement can override the default setting of the OPTCOMPIND environment variable while the routine that issues this statement is running in the current session.

That is, the scope of this session environment setting is local to the routine that issues the SET ENVIRONMENT OPTCOMPIND statement. That setting persists only until the routine exits, or until the same routine issues another SET ENVIRONMENT OPTCOMPIND statement, rather than persisting for the entire session. After that routine terminates, the setting reverts to the system default value that the OPTCOMPIND environment variable specifies, or else the value set by another method in the order of precedence described below in this topic.

Important:

No other option to SET ENVIRONMENT has a scope that is local to the routine that sets the value. The settings of all the other SET ENVIRONMENT options persist until the session ends, or until another SQL statement in the same session resets their value.

The system default value of OPTCOMPIND

This is the descending order of precedence for the default OPTCOMPIND value, from highest to lowest, if conflicting values have been set by different methods:

the OPTCOMPIND environment variable setting
OPTCOMPIND configuration parameter setting, if OPTCOMPIND is not set
2, if neither of the above are set.

Examples of resetting OPTCOMPIND

The following statement replaces the default setting with a purely cost-based optimizer strategy:

SET ENVIRONMENT OPTCOMPIND '2';

The following statement has the same effect as the previous example, unless transaction isolation level is Repeatable Read:

SET ENVIRONMENT OPTCOMPIND '1';

If isolation level is Repeatable Read, the query optimizer chooses index scans, This is the recommended setting for Repeatable Read, because it reduces the risk of lock contention among connections when hash-join execution paths set temporary locks on all records of joined tables.

The next example makes the query optimizer prefer nested-loop joins over other possible join methods:

SET ENVIRONMENT OPTCOMPIND "0";

If a routine has set the OPTCOMPIND session environment option to a numeric value appropriate for the next join query, after that transaction completes, the same routine can issue the following statement to restore the system default value:

SET ENVIRONMENT OPTCOMPIND DEFAULT;

For more information about the different join methods that the optimizer can choose, and the performance implications of the OPTCOMPIND session environment setting, see your Informix® Performance Guide.

For more information about the OPTCOMPIND configuration parameter, see your Informix® Administrator's Reference.

For more information about the OPTCOMPIND environment variable, see your Informix® Guide to SQL: Reference.