Using oracle hints

[McBugzz]

I need to make a small application that would demonstate the benefits from using hints i.e. I need a query that can be significantly optimized with hints, so that I could run it with and without hints and show the difference. Does anybody know an article of that subject or maybe some ideas?

Thanks

 

[sem]

IMHO in 10g optimizer is robust enough to let you get rid of hints at all. In most cases using hints in 9i+ helps in resolving issues that in fact are related to the quality/lack of stistics.
But if you need to demostrate the power of hints, just don't gather statistics on a large table and then make a query by unique key: most probably without INDEX hint you would get a full scan

Regards, Dima

http://seminihin.narod.ru