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
>

The hint then of course might be incorrectly specified. select /*+ index(emp) */ * from emp e where empno = 10 will not work as you have aliased the table, you should use the alias in your hint.
In this case you hint it to use *any* index, the optimizer may find using full table scan is cheaper. Happens to me occasionally.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Thu Sep 27 2001 - 22:44:35 CDT