OPTCOMPIND Environment Option (IDS)

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 subsequent queries.

• If the value is '0' then queries will use a nested-loop join where possible, rather than a sort-merge join or a hash join.
• If the value is '1' and the transaction isolation level is not Repeatable Read, the optimizer behaves as in setting '2' as described next; for any other isolation level, it behaves as in setting '0' above.
• If the value is '2' then 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.

For example, the following statement replaces whatever OPTCOMPIND setting was previously in effect with a purely cost-based optimizer strategy:

SET ENVIRONMENT OPTCOMPIND '2'

Use the DEFAULT keyword to restore the system default value, as described in the OPTCOMPIND section of the IBM Informix Guide to SQL: Reference.

For performance implications of the OPTCOMPIND option, see your IBM Informix Performance Guide.