Understanding ‘Why my index is not being used?’ is a big topic as there are many varied reasons for indexes not being used. The first thing you can do is to check whether your table is indexed or not.
Why SHOULD the index be used?
Oracle does not have to use an index simply because an index exists. If a query requires every row in the table to be queried (in table joins, for example), then why query all the rows in the index AND all the rows in the table? Ignoring the index in this case results in better performance. The optimizer makes decisions based on statistics regarding the suitability of various access methods, including indexes, for all queries and chooses the best one.
The table is indexed, isn’t it?
Check that the table which you believe should be accessed via an index actually has indexes defined on it. The indexes could have been dropped or could have failed to create – For example, it is possible, after importing tables, or performing loads, that indexes were not created due to errors (either software or human). The following query shows an example of checking for indexes:
SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;