Re: Why Oracle does not use index?

Which fields are in your primary key?? And are you referencing them in the WHERE clause of your SQL? I'm assuming its the Company_id. If it isn't, that will probably be why a full table scan is being done.

You'll normally find that the optimiser has to do a full table scan on something. Depending on how many rows is being returned from the company_btr table in relation to the 200,000 records in there, it may be more efficient for a full table scan to be done on the large table. (e.g If 120,000 rows are being returned from 200,000)

M

frank_z_j_at_hotmail.com (Frank Jiang) wrote in message news:<e4ffcd8f.0212031856.4012211d_at_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 Wed Dec 04 2002 - 02:31:00 CST