Home » SQL & PL/SQL » SQL & PL/SQL » What are the possible alternatives for rule hint (Oracle 9i)
What are the possible alternatives for rule hint [message #333741] Mon, 14 July 2008 05:40 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
What is the possible alternatives for rule hint and how if we need any index or histograms created.


We useing /*+ rule*/ expecially on sql with joins usng a date range.There are other places where it is being used on some complicated sql's as oracle cost based optimizer goes for a toss and fulll table scan.

I have a little knowledge on it.Need your suggesion.


Thanks in advance,
Oli


[Updated on: Mon, 14 July 2008 05:52]

Report message to a moderator

Re: What are the possible alternatives for rule hint [message #333744 is a reply to message #333741] Mon, 14 July 2008 05:56 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
use Rule Hint when you think you know your data better than Oracle.

In Higher oracle versions rule hint is getting replaced via cost based optimizer.

You should rely on that only because this is the better way.

Study DBMS_STATS and Cost Based Optimizer

CBO



Regards,
Rajat

[Updated on: Mon, 14 July 2008 05:57]

Report message to a moderator

Re: What are the possible alternatives for rule hint [message #333748 is a reply to message #333741] Mon, 14 July 2008 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have little knowledge, REMOVE all hints.

Regards
Michel
Re: What are the possible alternatives for rule hint [message #333752 is a reply to message #333744] Mon, 14 July 2008 06:27 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the response Raj..
I went through the link you provided.But still not clear how rule hint can be replaced by CBO

Can anyone help with some example?

Regards,
Oli
Re: What are the possible alternatives for rule hint [message #333753 is a reply to message #333748] Mon, 14 July 2008 06:29 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Mon, 14 July 2008 06:16
If you have little knowledge, REMOVE all hints.

Regards
Michel



Come on Michel!
Re: What are the possible alternatives for rule hint [message #333755 is a reply to message #333753] Mon, 14 July 2008 06:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Olivia wrote on Mon, 14 July 2008 13:29
Michel Cadot wrote on Mon, 14 July 2008 06:16
If you have little knowledge, REMOVE all hints.

Regards
Michel



Come on Michel!

Believe it or not, but this really is good advice. If you don't know a lot about optimizing queries and the way Oracle's optimizer works, don't mess with hints, as they can get your server to its knees, when (note: when, not if) used improperly.
Re: What are the possible alternatives for rule hint [message #333761 is a reply to message #333755] Mon, 14 July 2008 06:52 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Frank wrote on Mon, 14 July 2008 06:42
Olivia wrote on Mon, 14 July 2008 13:29
Michel Cadot wrote on Mon, 14 July 2008 06:16
If you have little knowledge, REMOVE all hints.

Regards
Michel



Come on Michel!

Believe it or not, but this really is good advice. If you don't know a lot about optimizing queries and the way Oracle's optimizer works, don't mess with hints, as they can get your server to its knees, when (note: when, not if) used improperly.




I want to understand and want to work on it.It would have been perhaps more helpful if I would get some documentation link instead of that.Raj provided me a link.But I think I need some more documentation and feel that I have to work hard to understand this.

Thanks,
Oli
Re: What are the possible alternatives for rule hint [message #333762 is a reply to message #333761] Mon, 14 July 2008 07:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't get me wrong; I don't want to offend you in any way, but considering what you have asked here so far, I'd say you are a junior developer.
Nothing wrong with that, we've all been there.
What we want to say is that there's a whole world out there you have to get through before you should be messing with hints.

Really appreciate your attitude though; there seem to be less and less developers so eager to learn.
If you still are determined to learn, start out by reading the Performance Tuning Guide

Good luck, and keep that spirit up Smile
Re: What are the possible alternatives for rule hint [message #333765 is a reply to message #333762] Mon, 14 July 2008 07:08 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@Frank,

I would reply back only after going through the manual.Anyway,I owe a BIG thanks to you for those encouraging words and providing me what I expected.

@Michel,

I prefer not to REMOVE them but to learn.



Regards,
Oli

[Updated on: Mon, 14 July 2008 07:08]

Report message to a moderator

Re: What are the possible alternatives for rule hint [message #333769 is a reply to message #333744] Mon, 14 July 2008 07:15 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:
use In Higher oracle versions rule hint is getting replaced via cost based optimizer.



But how? Any example if you have.

Thanks to you for providing the information.

Regards,
Oli






Re: What are the possible alternatives for rule hint [message #333792 is a reply to message #333769] Mon, 14 July 2008 08:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Oracle simply ignores the rule hint, since the rule based optimizer no longer is used.
Re: What are the possible alternatives for rule hint [message #333818 is a reply to message #333792] Mon, 14 July 2008 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Mon, 14 July 2008 15:00
Oracle simply ignores the rule hint, since the rule based optimizer no longer is used.

Not in the current versions.
One day maybe (and surely it will be) but for the moment rule hint is still taken into account as long as you use old features that is not partitioning and so on.

Regards
Michel


Re: What are the possible alternatives for rule hint [message #333821 is a reply to message #333753] Mon, 14 July 2008 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Olivia wrote on Mon, 14 July 2008 13:29
Michel Cadot wrote on Mon, 14 July 2008 06:16
If you have little knowledge, REMOVE all hints.

Regards
Michel



Come on Michel!

This is the best advice I can give you.
Hints should be used ONLY by optimizer expert once they fully understand Database Performance Tuning Guide.
99% of the time they are useless just misused because of lack on what is SQL, how it works and how Oracle optimizer works.
They are not silver bullet.
More, when you use them you have to test them and possibly change at each release, each version, each patchset. Do you really want this?
Don't learn hints, learn SQL.
I have a friend, I think he is one of the best in SQL world, he is able to optimize some SQL queries with just a glance to them, he is amazing and he never, I repeat NEVER, uses hints (or maybe he did it but I can't remember after the dozen of queries I saw him optimize).

Regards
Michel

Re: What are the possible alternatives for rule hint [message #333843 is a reply to message #333821] Mon, 14 July 2008 10:05 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
I have a friend, I think he is one of the best in SQL world, he is able to optimize some SQL queries with just a glance to them, he is amazing and he never, I repeat NEVER, uses hints (or maybe he did it but I can't remember after the dozen of queries I saw him optimize).

His name by any chance is Michael Cadot ?
Regards

Raj
Re: What are the possible alternatives for rule hint [message #333845 is a reply to message #333843] Mon, 14 July 2008 10:11 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it is Stephane Faroult.

You can hear him on YouTube:
http://www.youtube.com/watch?v=40Lnoyv-sXg
http://www.youtube.com/watch?v=GbZgnAINjUw
http://www.youtube.com/watch?v=y70FmugnhPU

He also wrote "The Art of SQL".

Regards
Michel

[Updated on: Wed, 05 May 2010 10:14]

Report message to a moderator

Previous Topic: Extracting data from clob gives ora-06502
Next Topic: declare and set a variable within a trigger?
Goto Forum:
  


Current Time: Fri Dec 09 23:36:27 CST 2016

Total time taken to generate the page: 0.10964 seconds