Re: Rule-based vs Cost-based Optimizer?

Tony Bravo (tbrav_at_ctp.com) wrote:
: Hi there,
 

: Does anyone have any comments regarding the cost based optimizer
: (which presumably now works bug-free) in 7.1.4.1.0 ?
 

: Has anyone done a comparison between rule and cost-based?
 

: Thanks
: Tony Bravo
: tbrav_at_ctp.com

We have been using the cost-based optimizer in a 7.0.16 and 7.1.3 instance, and have observed a number of extremely poor 'decisions' by the optimizer. In some situations, valid indexes were ignored on very large tables which resulted in full table scans. Merge-joins will also sort on full table scans of large objects. We ran the same queries with rule-based, and cut the run-time from hours to minutes.

We plan to run a formal test when we upgrade to 7.1.4. If the results are still mixed, we will probably switch back to rule-based. I have heard from a number of other installations which have already yanked the cost-based optimizer. It really is too bad that the CBO is still bug-ridden, since many installations run ad-hoc clients which do not allow for query tuning.

--

-------------------------------------------------------------------
Rich Bromberg                     |  dbINTELLECT Technologies, Inc.
Database Consultant               |  Lakewood, Colo.
                                  |  (303) 763-3822
                                  |  email: bromberg_at_dbintellect.com         
                                  |  fax: (303) 763-4262
-------------------------------------------------------------------