Re: Hints

sweidanz_at_yahoo.com wrote in message news:<Q8Ps7.32$uX3.677_at_nsw.nnrp.telstra.net>...
> Folks,
> Is it possible that Oracle optimiser would ignore the hint and does
> something different?
> Here is my case:
>
> - Oracle 8.1.6/ Solaris
> - Optimiser mode is CHOOSE
> - There are statistics for the tables in the query
>
> First run: (no hints)
> - execution plan says that the optimiser used the cost_based method.
> - it did a full table scan on table A.
>
> Second run: hint /*+ RULE */
> - The execution plan used an index in table A
>
> Third run: hint /*+ Index tableA(indexname) */
> - The optimiser did a FULL table scan on tableA.
>
> Just wondering if thats possible, that the optimiser would ignore the
> hint, or i might be missing something here?
>
> thanks,
> ZS

ZS, the manner in which you listed the index hint is wrong and should appear something like,

select /*+ INDEX(A INDEX_NAME) */
from table_a A
where ....

The optimizer will generally accept valid index hints if the plan chosen allows the use of the index. If the plan uses a hash join the index hint may not be applicable unless you also hint the sql to use a nested loop join making it possible for the CBO to use the index. You may also need to change the table join order to make the index available.

• Mark D Powell --