Could anyone please explain what are the oracle HINTS are mostly used for improve query perfomance ?

MIHIR KAR used Ask the Experts™
Which hints are used when table create internal join like (Hash, SHORT-MERGE & NESTED-LOOP) and they harm on database perfomance ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
The hints I've seen most often used are the INDEX and LEADING hints.  Trying to force a particular index to be used and/or change the order in which tables are joined.

In general, try to avoid using hints for production code.  Use them to experiment with different execution plans.  When you find a good one, then work on figuring out why the optimizer didn't generate that plan on its own without the hint.  What information does it not have?

Also, if you use hints, check the plan for all of the other hints that the optimizer puts in for you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial