Re: Oracle using indexes

I did some reading in last few days and found this "driving table" thing in a book. As Brian mentioned, the last (2nd) table in the FROM clause is the "driving table". The book says that Oracle performs full scan on the driving table. It seems true in most cases. However, I did run into a real world scenario (can't find that query any more) that Oracle performs an INDEX SCAN on the first table and ACCESS BY ROWID (with index scan) on the 2nd table.

It seems to me that Oracle optimizer has a mind of its own. Running explain plan is the only sure way to tune a query.

I am considering to change our database to be cost-based. How often do I need to analyze tables and indexes? Any suggestions?

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:21:48 CST