OPTCOMPIND environment variable

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 HCL OneDB™, the SET ENVIRONMENT OPTCOMPIND statement can set or reset OPTCOMPIND dynamically at runtime. This overrides the current OPTCOMPIND value (or the ONCONFIG configuration parameter OPTCOMPIND) for the current user session only. For more information about the SET ENVIRONMENT OPCOMPIND statement of SQL see the HCL OneDB Guide to SQL: Syntax.

For more information about the ONCONFIG configuration parameter OPTCOMPIND, see the HCL OneDB Administrator's Reference. For more information about the different join methods that the optimizer uses, see your HCL OneDB Performance Guide.