Influencing the choice of a query plan

The OPTCOMPIND environment variable and the OPTCOMPIND configuration parameter indicate the preferred join plan, thus assisting the optimizer in selecting the appropriate join method for parallel database queries. To influence the optimizer in its choice of a join plan, you can set the OPTCOMPIND configuration parameter.

The value that you assign to the OPTCOMPIND configuration parameter is referenced only when applications do not set the OPTCOMPIND environment variable.

Set OPTCOMPIND to 0 if you want the database server to select a join plan exactly as it did in versions of the database server prior to version 6.0. This option ensures compatibility with previous versions of the database server.

An application with an isolation mode of Repeatable Read can lock all records in a table when it performs a hash join. For this reason, you should set OPTCOMPIND to 1.

If you want the optimizer to make the determination for you based on cost, regardless of the isolation level of applications, set OPTCOMPIND to 2.

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

For more information about OPTCOMPIND and the different join plans, see The query plan.