Home » SQL & PL/SQL » SQL & PL/SQL » Cost based and Rule Based Optimization
Cost based and Rule Based Optimization [message #195980] Tue, 03 October 2006 08:02 Go to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi everybody
Can Any body guide me about the query optimization and difference between the Cost Based Optimization and Rule Based Optimization?

And What is the Impact of changing the order of tables in the From clause of a SELECT statement.




thanks
Re: Cost based and Rule Based Optimization [message #195982 is a reply to message #195980] Tue, 03 October 2006 08:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Query optimisation is a HUGE subject. Do you have any particular questions?

The RBO uses a simple set of rules to produce an execution plan for a query.
The CBO examines statistics about how the data is distributed in the tables involved in the query, and uses a very complex set of rules to derive an execution plan based on these statistics.

With the CBO the order of the tables in the query makes no difference at all.
It's been so long since I used the RBO that I honestly can't remember if the order of the tables makes a difference. I don't think it does, but I may be wrong.
Re: Cost based and Rule Based Optimization [message #196011 is a reply to message #195982] Tue, 03 October 2006 12:40 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Yes, the old RBO used to be influenced by the order of tables, in the absence of any other deciding factors such as an index. Annoyingly, it used the FROM list order in reverse, and to this day there are people still writing queries backwards out of habit, even though it makes no difference to the CBO and serves only to annoy the hell out of anyone trying to make sense of the code.

Use of the RBO is rather unusual these days. It doesn't know about any new features since Oracle 7.3, such as hash joins, for example.

[Updated on: Tue, 03 October 2006 12:41]

Report message to a moderator

Re: Cost based and Rule Based Optimization [message #196066 is a reply to message #196011] Wed, 04 October 2006 00:15 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi
I will grateful if anyone tell me a good link to know the basics of the CBO and RBO ?
Re: Cost based and Rule Based Optimization [message #196071 is a reply to message #196066] Wed, 04 October 2006 00:59 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Read the section Optimizing SQL Statements in the Performance Tuning Guide.

For an in-depth study, look at "Cost-Based Oracle Fundamentals" by Jonathan Lewis (Apress, 2006).
Re: Cost based and Rule Based Optimization [message #196193 is a reply to message #196066] Wed, 04 October 2006 08:07 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
b_chugh wrote on Wed, 04 October 2006 01:15

hi
I will grateful if anyone tell me a good link to know the basics of the CBO and RBO ?


As William has said, it cannot make use of any post 7.3 features, so why would you want to know the ins and outs of RBO? Are you using a version pre 7.3? Or are you building a museum and trying to stock it with historical items Wink

[Updated on: Wed, 04 October 2006 08:08]

Report message to a moderator

Re: Cost based and Rule Based Optimization [message #196201 is a reply to message #196193] Wed, 04 October 2006 08:27 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Some places are stuck on the RBO because they use 3rd party applications that use it.
One place I worked had a system using the RBO on 9.2.0.6, simply because they'd never had the time to do the testing needed to move the system to CBO, and it worked acceptably fast as it was.
Previous Topic: How to run PL/SQL scripts
Next Topic: Returning Recordset from PL/SQL
Goto Forum:
  


Current Time: Tue Dec 06 16:26:59 CST 2016

Total time taken to generate the page: 0.08844 seconds