Re: Why not using index in execution plan?

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 Wed Dec 04 2002 - 17:22:30 CST