Home -> Usenet -> comp.databases.oracle -> Cost vs. Rule based optimizer

Cost vs. Rule based optimizer

From: kevin patrick cutress <kcutress_at_cs.uno.edu>
Date: 1995/04/20
Message-ID: <1995Apr20.021624.22182_at_cs.uno.edu>#1/1 






I've just installed Oracle 7 on a CDC 4460 runing EPIX 2.1.1.  Oracle's 
promotional literature and the Oracle 7 docs promote the benefits of the cost
based optimizer.  They even say that the rule based optimizer will be removed
from future releases.  I've analyzed all of our tables and a check of the data
dictionary confirms that all tables and indexes have been analyzed.  I've even
computed statistics instead of estimating.  In spite of this, the cost based
optimizer is doing a very poor job.  Has anyone else had this experience?



For example, I have a table with 705,000 records.  Approximately 12,000 records
have a commodity code equal to 33440.  Approximately 28,000 records have a
state code equal to AK.  The query   select sum(tons) from detail94 where
commodity = 33440 and state = 'AK'  consistently takes 3 to 5 minutes using the
cost based optimizer and less than 30 seconds using the rule based optimizer.
Both fields in the where clause are indexed.



Does anyone have any ideas on why there should be such a difference?  Can I 
make the cost based optimizer work or should I abandon the effort and make 
rule based optimization the default?
Received on Thu Apr 20 1995 - 00:00:00 CEST












Original text of this message