Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Why Oracle does not use index?

Why Oracle does not use index?

From: Frank Jiang <frank_z_j_at_hotmail.com>
Date: 3 Dec 2002 18:56:02 -0800
Message-ID: <e4ffcd8f.0212031856.4012211d@posting.google.com>


Hello
I have a large table which has B+ tree index on PK. When I join with another smaller table, I check the execution plan, it use a nested join loop. To my supprise, smaller table is access by index but large table is access by a full scan.

Can anybody give me reason?

accounts_btr(4000 tuples)
company_btr(200,000 tuples)
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
Received on Tue Dec 03 2002 - 20:56:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US