Re: Why not using index in execution plan?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 5 Dec 2002 09:04:09 -0000
Message-ID: <3def168a$0$227$ed9e5944@reading.news.pipex.net>

There isn't *an* answer to this question, there are a large number of factors that contribute to the access paths that the optimiser will take.

A good introduction is here
http://download-west.oracle.com/docs/cd/A97630_01/server.920/a96533/optimops .htm#51003

For a particular query you can also issue

alter session set events '10053 trace name context forever, level 2'; and then explain plan the relevant query (so it has to be parsed) you will dump information that the optimiser has used to derive the execution plan it uses to a trace file in user_dump_dest. This output may or may not make much sense.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
"Amir Pashazadeh" <pasha_at_abdnet.com> wrote in message
news:asm37h$slj7s$1_at_ID-170682.news.dfncis.de...


> I'm interested in this topic, so what is the answer to this question?

>

> Tanx,

> Amir,

>

> "Brian E Dick" <bdick_at_cox.net> wrote in message

> news:YodH9.68669$wc2.3470066_at_news2.east.cox.net...

> > Your question should be "What query constructs cause the database

> optimizer

> > to consider using an index?"

> >

> > "Frank Jiang" <frank_z_j_at_hotmail.com> wrote in message

> > news:e4ffcd8f.0212031443.79231f3a_at_posting.google.com...

> > > Hello,

> > > This is for my database class. Assignment due in 3 days. Please help

> > > me.

> > >

> > > table COMPANY_BTR (200,000 rows) B+ tree index on Company_ID(PK)

> > > table ACCOUNTS_BTR (4,000 rows) B+ tree index on Company_ID,

> > > Account_ID(PK)

> > >

> > > query:

> > > SELECT A.account_id, C.company_id, C.name, C.state, C.city

> > > FROM accounts_btr A, company_btr C

> > > WHERE A.company_id = C.company_id

> > > AND C.state = 'CA'

> > > AND C.city = 'Lakewood';

> > >

> > > EXCUTION_PLAN

> > >

> > >   SELECT STATEMENT

> > >     TABLE ACCESS BY INDEX ROWID ACCOUNTS_BTR

> > >       NESTED LOOPS

> > >         TABLE ACCESS FULL COMPANY_BTR

> > >         INDEX RANGE SCAN ACCOUNTS_BTR_CID

> > >

> > > My question is why Oracle does a full table scan on big table without

> > > using index. I believe Oracle must have a reason to do this.

> > >

> > > Another question is that is the full table scan step the inner loop?

> > >

> > > Thank you.

> > >

> > > Frank Jiang

> >

> >

>

>

Received on Thu Dec 05 2002 - 03:04:09 CST