Re: Why not using index in execution plan?

On 3 Dec 2002 14:43:30 -0800, frank_z_j_at_hotmail.com (Frank Jiang) wrote:

>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

Apparently, given the nature of your questions, the only thing they learned you is to have others do your work for you for free. The questions above should have been addressed in the class, and this is not the place to have your homework done.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Dec 03 2002 - 17:17:08 CST