OPTCOMPIND

You can set the OPTCOMPIND environment variable so that the optimizer can select the appropriate join method.

                       .-2-.
>>-setenv--OPTCOMPIND--+-1-+-----------------------------------><
                       '-0-'

0

A nested-loop join is preferred, where possible, over a sort-merge join or a hash join.

1

When the isolation level is not Repeatable Read, the optimizer behaves as in setting 2; otherwise, the optimizer behaves as in setting 0.

2

Nested-loop joins are not necessarily preferred. The optimizer bases its decision purely on costs, regardless of transaction isolation mode.

When OPTCOMPIND is not set, the database server uses the OPTCOMPIND value from the ONCONFIG configuration file. When neither the environment variable nor the configuration parameter is set, the default value is 2.

For more information on the ONCONFIG configuration parameter OPTCOMPIND, see the IBM Informix: Administrator's Reference. For more information on the different join methods that the optimizer uses, see your IBM Informix: Performance Guide.