RE: rm RULE based optimizer != GOOD IDEA

But I don't think in 10g you can count on the kernel always returning the right rows when you use it...

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Visit www.hotsos.com for curriculum and schedule details...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Fontana
Sent: Thursday, April 21, 2005 4:59 PM
To: Ethan.Post_at_ps.net; oracle-l_at_freelists.org Subject: RE: rm RULE based optimizer != GOOD IDEA

If you could post your optimizer settings, I think I can explain this.

For example, in SQLPLUS, enter

Show parameter opt

Sometime you have to tune your optimizer settings to match your query Mix in the real world.

<<Now that ran really slow using the COST optimizer...

but when we added the RULE hint, it ran about much faster (50 sec to < 1 sec).

Of course we can't do that so we spent a lot of time writing the query in different ways, moved the select count(*) into an inline view and joined to that, query dropped from 50 seconds to about 20 seconds but did not approach the sub-second performance of the RULE method.

The explain plan was not helpful because it looked the same as the plan generated using RULE but the difference was in the way the select count() was being handled, and the plan wasn't showing us that.

So what is the gist of this post? Well, I am sure if we continued to look at this that we might come up with a solution but why waste all that time when /*+RULE */ does the trick? What would be the point of Oracle removing something that time and time again demonstrates it's usefulness like this? It essentially (in this particular case) is a GO_FASTER hint and it worked. So maybe they will get rid of the ability to set the optimizer mode to RULE but this will hopefully always be there at the statement level.
--

http://www.freelists.org/webpage/oracle-l>>

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 22 2005 - 16:25:13 CDT