Re: cost based optimizer vs rule based optimizer

Generally you should use the cbo instead of the rule based optimizer. But it is important that you have correct statistics in your database. If you have in many cases the cbo chooses automatic a better plan than the rule based optimizer. We have build sites, especially oltp based, which runs automatic faster with cbo.  In some cases cbo "works wrong". Then you have to use hints to force cbo to choose a better plan. (The wrong thing is mostly a silly full table scan!)

If you use rule based optimizing, some execution options for queries will never used. I dont't know exactly, but i think there are the options hash joins and bitmap plans which will not work. And if the rule based optimizer will not work, you can use hints or rewrite your statement (reorder the from clause etc).

So, with cost based, you can use all things you can do with rule based. But the other way doesn't work. And may be Oracle will not support the rule based optimizer further more.

Andreas Prusch

In article <2MGe1.29$%3.1580015_at_news.ipass.net>,   "David Sisk" <davesisk_at_ipass.net> wrote:
>
> Hi Kel:
>
> Cost seems to work best for us. I think they both have their bugs, but CBO
> supports more advanced features such as table and index partitioning, etc.
>
> Good luck,
> Dave
>
> Kel Bahi wrote in message <3576EE22.3AA6_at_creo.com>...
> >Hi
> >Does anyone have any opinions on which optimizer to use?
> >Are they comparable? Is one always better than the other?
> >Does it depend on the database ( volume, rate of change)
> >
> >Any info appreciated.
> >BTW we are using oracle 8.0.3 under NT.
> >
> >Thanks
> >Kel Bahi
> >Creo Products
> >kbahi_at_creo.com
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Jun 08 1998 - 09:25:32 CEST