Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Cost vs rule based optimizer
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,
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,
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?