Re: Optimizer Hints

In article <49043a$7rm_at_asbe05.phx1.aro.allied.com>,

   John Cutright (AlliedSignal Engines) wrote:
>Would someone give me the syntax for using the "hints" feature on a select statement
>(i.e. forcing the optimizer to use a particular index). I don't have a full doc set and the
>books that I do have don't mention how to do it.
>
>Thanks,

Hi,

The only way to force the optimizer to use a certain index or execution plan is to build your statements thus that an index is used. For example, if your database is configured to perform rule-based optimization and you have a concatenated index on a certain table, make sure all the columns you've indexed are in the where-clause :

table1: index on col1 (VARCHAR2), col2(NUMBER) and col3(NUMBER)

statement : Suppose you want to select rows where col1 has a certain value :

select

        col1, col2
from

        table1
where

	col1 = <value> and
	col2 > 0 and
	col2 > 0

This forces the rule-based optimizer to use the index you defined. Unfortunately these 'hints' aren't described in the docs Oracle supplies, I know them from a training I had at Oracle.

If you're using the cost-based method, regularly ANALYZE your table :

ANALYZE TABLE table1 COMPUTE STATISTICS.

This collects statistics about your table and saves them in the datadictionary tables. These statistics are then used by the optimizer. You can examine execution plans using the 'EXPLAIN PLAN FOR <statement>'. This puts the execution plan in the table PLAN_TABLE (which has to be created with the script $ORACLE_HOME/rdbms/admin/utlxplan.sql).

Hope this helps,
Rene.

| Rene Hoekstra
| E-Mail : Rene.Hoekstra_at_rivm.nl
| National Institute of Public Health and Environmental
| Protection (R.I.V.M.)
| Bilthoven, The Netherlands
============================================================
      " Alleen slaapwandelaars kunnen in slaap vallen "
============================================================