Home » Other » Training & Certification » Hints in oracle
Hints in oracle [message #209681] Sat, 16 December 2006 13:52 Go to next message
Messages: 9
Registered: July 2006
Location: pune
Junior Member
what r the meanings of HINTS,why and how r they used in oracle,as a plsql developer how can i use?
Re: Hints in oracle [message #209708 is a reply to message #209681] Sun, 17 December 2006 05:24 Go to previous messageGo to next message
Messages: 826
Registered: December 2005
Senior Member
Hints are suggestions to the optimizer, to choose a particular execution path etc...they should not be regularly used but , they should be employed only when needed...when a query without a hint is not doing well and you have had discussions with DBA also , after that the DBA might , if required, tell you to use it..
Re: Hints in oracle [message #209824 is a reply to message #209708] Mon, 18 December 2006 00:42 Go to previous message
Messages: 7064
Registered: December 2001
Senior Member
Start here.

from the Oracle9i Database Performance Tuning Guide and Reference
Optimizer hints can be used with SQL statements to alter execution plans. This chapter explains how to use hints to force various approaches.
Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

You can use hints to specify the following:
  • The optimization approach for a SQL statement
  • The goal of the cost-based optimizer for a SQL statement
  • The access path for a table accessed by the statement
  • The join order for a join statement
  • A join operation in a join statement

Personally, I use as few hints as possible. I try to accomplish the best performance without applying hints. Sometimes though, a hint can help you out but in most cases you don't need it. If your statistics are up to date and your SQL is well written, you can leave it to the optimizer.

Question about hints
Hints When to use

Previous Topic: oracle for linux system administrator
Next Topic: Doubt about oracle developer exam
Goto Forum:

Current Time: Sat Mar 06 08:18:48 CST 2021