Why not using index in execution plan?

From: Frank Jiang <frank_z_j_at_hotmail.com>
Date: 3 Dec 2002 14:43:30 -0800
Message-ID: <e4ffcd8f.0212031443.79231f3a@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 - 16:43:30 CST