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.

On Dynamic Server, the SET ENVIRONMENT OPTCOMPIND statement can set or reset OPTCOMPIND dynamically at runtime. This overrides the current OPTCOMPIND value (or the the ONCONFIG configuration parameter OPTCOMPIND) for the current user session only. For more information on the SET ENVIRONMENT OPCOMPIND statement of SQL see the IBM Informix: Guide to SQL Syntax.

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.