Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Rule Based Optimization

572601May 9 2007 — edited May 9 2007
Hi,

Rule Based Optimization is a deprecated feature in Oracle 10g.We are in the process of migrating from Oracle 9i to 10g.I have never heard of this Rule based Optimization earlier.I have googled for the same.But, got confused with the results.

Can anybody shed some light on the below things...

Is this Optimization done by Oracle or as a developer do we need to take care of the rules while writing SQL statements?

There is another thing called Cost Based Optimization...
Who will instruct the Oracle whether to use Rule Based Optimization or cost Based Optimization?

Thanks & Regards,
user569598

Comments

ViragSharma
Check following link this has good details

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/burleson_cbo_pt1.html

Cheer,
Virag

Message was edited by:
virag_sh
Gurjas
hi
optimizer_mode paramete of db decide wheather to you rule base optimization or cost base.
The Human Fly
Hope the following explanation would be helpful.

Whenever a statement is fired, Oracle should goes through the following stages:

Parse -> Execute -> Fetch (fetch only for select statement).

During Parse, Oracle first evaluates, Syntatic checking (SELECT, FROM, WHERE, ORDER BY ,GROUP and etc) and then Semantic Checking (columns names, table name, user permission on the objects and etc). Once these two stages passes, then, it has to decided whether to do soft parse or hard parse. If similar cursor(statement) doesn't exits in the shared pool, Oracle goes for Hard parse where Optimizer comes in picture for generating query plan.
Oracle has to decide either RBO or CBO. It also depends on the OPTIMIZER_MODE parameter value. If RULE hint is used, RBO will be used, if there are no statistics for those tables involved in the query, Oracle decides RBO, (condition applies). If statistics are available, or dynamic samplying is defined then Oracle use CBO to prepare the Optimal execution plan.

RBO is simply relies on set of rules where CBO relies on statistical information.


Jaffar
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 6 2007
Added on May 9 2007
#general-database-discussions
3 comments
187 views