Effect of OPTCOMPIND on the Query Plan

The OPTCOMPIND setting influences the access plan that the optimizer chooses for single and multitable queries, as the following sections describe.

You can use the SET ENVIRONMENT OPTCOMPIND command to change the value of OPTCOMPIND within a session. For more information on using this command, see SET ENVIRONMENT OPTCOMPIND.

Single-Table Query

For single-table scans, when OPTCOMPIND is set to 0 or 1 and the current transaction isolation level is Repeatable Read, the optimizer considers the following access plans:

• If an index is available, the optimizer uses it to access the table.
• If no index is available, the optimizer considers scanning the table in physical order.

When OPTCOMPIND is not set in the database server configuration, its value defaults to 2. When OPTCOMPIND is set to 2 or 1 and the current isolation level is not Repeatable Read, the optimizer chooses the least expensive plan to access the table.

Multitable Query

For join plans, the OPTCOMPIND setting influences the access plan for a specific ordered pair of tables. Set OPTCOMPIND to 0 if you want the database server to select a join method exactly as it did in previous versions of the database server. This option ensures compatibility with previous versions.

If OPTCOMPIND is set to 0 or set to 1 and the current transaction isolation level is Repeatable Read, the optimizer gives preference to the nested-loop join.

If OPTCOMPIND is set to 2 or set to 1 and the transaction isolation level is not Repeatable Read, the optimizer chooses the least expensive query plan from among those previously listed and gives no preference to the nested-loop join.