Re: Using hints with Oracle9i MERGE

The commonest reason why Oracle will refuse to use a hinted index when the hint syntax is correct is that the index has no columns declared as NOT NULL.

Niall is correct - barring a few bugs, oddities regarding internal rewrites that take place before optimisation, and the cases where Oracle stops optimising before it gets to a point where your hint is relevant - hints are directives.

If hints were not directives, how could Oracle choose to ignore the hint ? Presumably by deciding that another path was better. But then hints would always be redundant, because Oracle would only obey them when they hinted the path that Oracle considered to be the best path - which is the path that Oracle would have taken without the hint. If this were the case, the only hints that could be of use would be the hints telling Oracle to use some feature that was a feature (e.g. unnesting) that was 'uncosted' in your specific version of Oracle

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


DA Morgan wrote in message <3E4CB76B.16BBD96B_at_exesolutions.com>...


>

>I have heard this before but it doesn't jive with my experience. I

have put


>together demos many times where a table exists, a simple index

exists, and a


>hint to use the index is ignored because Oracle determines the full

table scan


>to be more efficient.

>

>If I am incorrect I would be fascinated to see some examples.

>