| Hints in oracle [message #209681] |
Sat, 16 December 2006 13:52  |
parmaleuday
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  |
orausern
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 |
 |
Maaher
Messages: 7065
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
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
