Sounds like you might have statistics computed for one or both tables, and
the optimizer figures in can just suck the whole description_table into
memory (only 2000 rows). The range scan sounds odd if you really have a
unique index on c.course. Try the RULE hint and reorder the tables in the
FROM clause.
select /*+ rule */
c.course, t.description
from description_table t, course_table c where
c.course = 'FRENCH100' and
t.course = c.course;
--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
adpctre_at_umassp.edu wrote in message <7496cg$gbm$1_at_nnrp1.dejanews.com>...
>I'm running the following PL*SQL command
>
>select
> c.course, t.description
> from course_table c, description_table t where
>c.course = t.course and
>c.course = 'FRENCH100';
>
>The course_table has 60000 records, the description_table has 2000.
>There is a unique index of "course" on the course_table.
>"COURSE" is also a primary key to the desription_table.
>
>It takes a long time to run... so I used the EXPLAIN_PLAN to try and figure
>out what it's doing.
>
>It's using a RANGE SCAN to get the course_table data,
> BUT,
>it uses a FULL scan to get the matching description on the
description_table.
>
>Either command by itself runs fast. It's only when doing the join that it
>takes a long time and does the full scan.
>
>Does anyone know how to make it use the primary key on the
description_table?
>(I've tried changing the order of the select and where clause, using
various
>HINTS, etc.)
>
> Thanks.
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Dec 04 1998 - 21:26:39 CST
