You may have a question that why oracle uses full table scan (fts) sometimes instead of using index, where you have index.
A full table scan perform the sequential reads, many blocks simultaneously from disk and index scan read single block, random IO’s. Sequential read is a fastest type of IO since it reads many blocks at a time. To read single block is a slow process.
Let's take a small example. Imagine a table has 1000 blocks with 1000 values and we will read 250 rows (25%) from that table using index. Suppose the buffer cache can hold 100 blocks. If we use index then the index might tell, go to block 1, get that row, go to block 2, get that row … go to block n, get that row. Imagine now in the buffer cache block 1-100 does not exist, block 101-200 exist and we have 50 more rows to read. Oh no, it might tell to go block n to re-read the block again from the disk that we already read since we don’t have that in buffer cache. In that scenario Oracle optimizer will do the full table scan instead of using the index scan since a full table scan will take less IO more efficiently.
The selectivity of index also depends on the ration of the distinct values / Total Number of Rows in a table. The ideal selectivity is 1 which can gain only by unique index on not null columns. The selectivity is good when few rows have same value. I had a question from developers that the table in production database using the index where the same table in stage database is not using the index. I have used that as an example here.
How to measure Index selectivity:
Selectivity = DISTINCT_KEYS / NUM_ROWS
-- Stage Database
SQL> select distinct_keys from user_indexes where table_name = 'TRANSACTION' and index_name = 'IDX_ CONSUMERID';
DISTINCT_KEYS
-------------
1156864
SQL> select num_rows from user_tables where table_name = 'TRANSACTION';
NUM_ROWS
----------
5543475
Selectivity = DISTINCT_KEYS / NUM_ROWS = 1156864/5543475 = 0.20
-- Production Database
SQL> select distinct_keys from user_indexes where table_name = 'TRANSACTION' and index_name = 'IDX_CONSUMERID';
DISTINCT_KEYS
-------------
810368
SQL> select num_rows from user_tables where table_name = 'TRANSACTION';
NUM_ROWS
----------
833234
Selectivity = DISTINCT_KEYS / NUM_ROWS = 810368/833234 = 0.97
In the above example production database used index because the selectivity is good which is 0.97 and in stage database the table did not used index because the selectivity was bad which is 0.20. So in stage database Oracle optimizer choose to use fts instead of index scan since index scan will take much more IO.
Conclusion: Index might not use by your query if the selectivity is bad or if it’s read too many rows which takes more IO than full table scan. You can force oracle to use index by using Hint.