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 Tue Dec 03 2002 - 20:04:40 CST
