Home » RDBMS Server » Performance Tuning » hint usage.
hint usage. [message #227455] Wed, 28 March 2007 05:21 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi.
When we need to use hint in query? please explain me with some example.

Thanks,
Shahnaz.
Re: hint usage. [message #227459 is a reply to message #227455] Wed, 28 March 2007 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Never unless you know how the optimizer works and what are the effects of each hint.

Regards
Michel
Re: hint usage. [message #228727 is a reply to message #227459] Tue, 03 April 2007 23:57 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi Michel,
Could you please list out what are the different hints available for the cost based optimizer and show with some example in which situation we use which hint?

Thanks,
Shahnaz.
Re: hint usage. [message #228741 is a reply to message #228727] Wed, 04 April 2007 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's an Alphabetical Listing of Hints.

Regards
Michel
Re: hint usage. [message #228971 is a reply to message #227455] Wed, 04 April 2007 14:01 Go to previous message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Update your statistics and Oracle will really do a great job of optimising a query.
Hints: once in a great while it helps to make Oracle use an index that it was not going to. Or to specify join or cartanze (sp?) joins.
Almost always Oracle's optimizer will do a great job with out hints. In fact, certain cases where indexes are built and then dropped it hinders performance.
Learn how to read the query plan before attempting to change the query. In some of the later versions you can develop a plan for a particular sql statement and then anytime Oracle sees that sql it uses the plan.

[Updated on: Wed, 04 April 2007 14:01]

Report message to a moderator

Previous Topic: Table Partition - Suggestion required
Next Topic: Partitioning a table- "newbie" help
Goto Forum:
  


Current Time: Mon Dec 05 13:21:01 CST 2016

Total time taken to generate the page: 0.09918 seconds