Re: Why Oracle does not use index?

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:ask50l$jvs$1_at_ctb-nnrp2.saix.net...
<skip excellent explanation>
> If you have a SI (secondary index) on STATE and CITY, then that would have
> been used instead of the full table scan.
>
> However - given the number of STATE values, this index is likely a
candidate
> for a bitmap index instead.

Unless the table is subject to frequent inserts and deletes which seems probable to me given the names of the objects.

I'd expect that *for this query* you need to as others have said.

1. create a secondary index on the company_btr table
2. Gather stats for the CBO. This query is as Billy said reading each row of the 200,000 row table and looking for matches, One would hope that it would (in the absence of the index folk are suggesting) read the 4000 row table and use that to drive the query, the reason it doesn't is almost certainly that you haven't told Oracle that it is a 200,000 row table.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************