Re: Oracle using indexes

Here you go.

Execution Plan

   0 SELECT STATEMENT Optimizer=RULE    1 0 NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'TBL2'
   3    1     INDEX (UNIQUE SCAN) OF 'SYS_C00614077' (UNIQUE)

In article <950214257.4952.0.nnrp-03.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> If you have used explain plan
> why not publish the plan instead
> of describing one line of the plan.
>
> Presumably the query is scanning
> tbl2 and indexing into tbl1 using
> the primary key on tbl1.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Christ Follower wrote in message <87v26h$ns5$1_at_nnrp1.deja.com>...
> >It is rule based by inquiring v$parameter.
> >
> >In article <38A225E1.125AE759_at_www.cis.com.ph>,
> > Michael Puente <mcpuente_at_www.cis.com.ph> wrote:
> >> I think your using cost-based optimizer.
> >> If the tables are small ( a few blocks ), it faster to do a full-
table
> >> scan. Also in using Cost-based approach Oracle uses the index only
> >if the
> >> rows returned are less than 10 % (value differ between versions).
> >>
> >> Christ Follower wrote:
> >>
> >> > I use explain plan to verify how Oracle utilizes indexes and
found
> >an
> >> > interesting scenario.
> >> > CREATE TBL1 (
> >> > KEY1 VARCHAR2(10) PRIMARY KEY,
> >> > FLD2 VARCHAR2(5));
> >> > CREATE TBL2 (
> >> > KEY1 VARCHAR2(10) PRIMARY KEY,
> >> > FLD2 VARCHAR2(5));
> >> >
> >> > EXPLAIN PLAN SET STATEMENT_ID='TEST' INTO PLAN_TABLE FOR
> >> > SELECT A.KEY1
> >> > FROM TBL1 A, TBL2 B
> >> > WHERE A.KEY1 = B.KEY1
> >> >
> >> > It reveals Oracle is performing a full table scan on TBL2. I
wonder
> >why
> >> > Oracle not using the primary key index since the values in
> >TBL2.KEY1 is
> >> > already in the index (smaller than scanning the entire table).
> >> >
> >> > Comment?
> >> >
> >> > Sent via Deja.com http://www.deja.com/
> >> > Before you buy.
> >>
> >>
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 11 2000 - 16:13:14 CST