Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Cost vs rule based optimizer

Cost vs rule based optimizer

From: Dagmar Murray <dmurray_at_purdue.edu>
Date: 1998/03/03
Message-ID: <6dh859$f60@mozo.cc.purdue.edu>#1/1

I am wondering why the performance I get with the cost-based optimizer is so poor compared to the performance I get with the rule-based optimizer.

I'm working with 2 tables: student and stu_course

student looks like this:
CREATE TABLE STUDENT(

 ID                INT NOT NULL,
 CURR              VARCHAR2(3) NOT NULL,
 REG_CLASS         VARCHAR2(2) NULL,
 LNAME              VARCHAR2(20) NOT NULL,

...and more fields

There are indexes on id and lname and the table contains about 35,000 records.

The stu_course table looks like this:
CREATE TABLE STU_COURSE
(

 ID              INT NOT NULL,
 COURSE          VARCHAR2(8) NOT NULL,
 CREDIT          NUMBER(3,1) NULL,

...and more fields

There are indexes on id and course and the table contains about 220,000 records

I want to execute the following query:

select student.id, student.curr, student.reg_class, sum(credit) from student, stu_course
where student.id = stu_course.id(+)
group by student.id, student.curr, student.reg_class;

With the cost-based optimizer on (and statistics have been computed), it uses the following plan:

OPERATION OPTIONS OBJECT_NAME POSITION

-------------------- -------------------- -------------------- --------
SELECT STATEMENT     [null]               [null]                  #####
  SORT               GROUP BY             [null]                      1
    HASH JOIN        OUTER                [null]                      1
      TABLE ACCESS   FULL                 STUDENT                     1
      TABLE ACCESS   FULL                 STU_COURSE                  2

Executing this statement takes hours.

Turning off the cost-based optimizer and using the rule-based, this is the plan:

SELECT STATEMENT     [null]               [null]               [null]
  SORT               GROUP BY             [null]                      1
    NESTED LOOPS     OUTER                [null]                      1
      TABLE ACCESS   FULL                 STUDENT                     1
      TABLE ACCESS   BY INDEX ROWID       STU_COURSE                  2
        INDEX        RANGE SCAN           SC_ID                       1

Results are returned in 2-3 minutes.

I have many other cases where the rule-based optimizer seems to make a more sensible decision about how to execute the query and gives results in a reasonable amount of time. This example seemed simple enough to present here.

My question is: if Oracle is encouraging us to use the cost-based optimizer why am I getting such poor performance using it? Is there something I'm missing here?




Dagmar C. Murray, Senior Software Engineer Space Management & Academic Scheduling
Purdue University
W. Lafayette, IN 47907-1128
E-mail: dmurray_at_purdue.edu Received on Tue Mar 03 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US