An index hint instructs the optimizer to consider the use of an indexed table scan for a specified table. You can optionally specify one or more indexes in the hint. If no indexes names are specified in the hint, Oracle optimizer evaluates the cost of all indexes for a table and uses the most efficient.
Instructing the optmizer to use an index is just a hint, it’s Oracle optimizer decision to make use of an index or not. If the Oracle optimizer recognizes that the cost of full table scan is cheaper than using an index, your index hint will be ignored. And there are more factors that optimizer may favor the use of full table scan rather than using an index.one among them is db_file_multiblock_read_count initiliazation parameter.
db_file_multiblock_read_count is a parameter which specifies the number of blocks than can be read by Oracle simultaneously.
If you increase the value in the db_file_multiblock_read_count rather than the default value, then Oracle favours more of full table scan’s rather than index usage. If you decrease the value in the db_file_multiblock_read_count , then Oracle favours more of index usage rather than full table scans.