Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Why Oracle does not use index?
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_CIDReceived on Tue Dec 03 2002 - 20:56:02 CST