CBO - Help

Not to mention that the Cost Based Optimizer needs the table and index statistics to be valid. If your table has significant transaction traffic then the stats may be stale. If you haven't gathered stats yet, you should. Just because you have an index doesn't mean it's any good, is it the right kind of index? on the right field? What about other tables involved in the query? Oh, don't forget that using functions on fields in the where clause will tend to prevent Oracle from using indexes on those fields.

If the data that you want is scatter gun distributed through your 165 million rows, and you're retrieving a significant amount of data, even if the number of rows retrieved isn't a large % of the table, the total cost of reading the index(es) and the table reads may still be greater than the full table scan.

Self joins on the table would hurt too.

Techniques to consider;

partitioning

bitmap indexes