Re: Cost vs rule based optimizer

The first question (and it's a big one), do you have statistics turned on for the table. Without them, who know what the optimizer will do. In article <6dh859$f60_at_mozo.cc.purdue.edu>,   "Dagmar Murray" <dmurray_at_purdue.edu> wrote:
>
> 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
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Mar 04 1998 - 00:00:00 CST