I am having a table which contains 80k to one l lakh records. While running explain plan query its not using indexes. And it seems, its doing full table scan.
I am not sure why oracle is not using indexes? Am I missing anything here?
Thank you, Regards,
BI DWH BALA
It could be a cardinality issue. Let's say you have a table with STAT_CODE defined where STAT_CODE is either 'A' or 'C' (Active or Closed).
If half are 'A' and half are 'C', an index on that column is probably useless, it's easier to get the records with a scan than to go through the overhead of using the index. The cutoff point varies by datamanager and other factors but a scan is probably useful until fewer than 25% of the rows would be qualified by checking the index.
Of course, compound indexes and other thing change the situation but generally, an index needs some uniqueness to be useful.
I'm not an Oracle expert, by any means, but I'd bet Oracle also has trouble when datatypes are badly matched. I've also seen scans generated on poorly defined queries.
I have found a couple of things that have helped me get rid of table access full, they may help you, they may not:
1) Ensure that all the columns used from the table are covered by an index. 2) Make sure that you reference the columns - either in the select, from or where clauses - in the same order as they are specified in the index. 3) Don't expect upper(....) etc to make use of your indexes. If you need to use upper, consider a function based index.
Consider the amount of data in the table, as Hillbilly says above, sometimes it's quicker for Oracle to load the whole table than to use the index.
