rm RULE based optimizer != GOOD IDEA

  • From: "Post, Ethan" <Ethan.Post@xxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 21 Apr 2005 16:46:38 -0500

Just got finished working on a little SQL perf problem.  The query was
something like...

select a.col, b.col, (select count(distinct foo) from tbl3 where
batch_id=3Db.batch_id) errors
  from tbl1 a,
       tbl2 b
 where a.batch_id=3Db.batch_id
   and a.status not in ('X','Z')=20
   and b.trans_type=3D'Q';

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

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.

Other related posts: