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 this configuration parameter is referenced only when applications do not set the OPTCOMPIND environment variable.
You can 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 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, it is recommended that you 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.
For more information on OPTCOMPIND and the different join plans, see The Query Plan.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]