Using index hints
Using index hints, you can hint the optimizer to use or ignore indexes. This is different from optimizer hints. In optimizer hints, you hint the optimizer to use or ignore certain optimization methods. Index and optimizer hints can be used separately or together to achieve the desired plan. Index hints are specified following a table name.
When you are executing a complex query involving multiple table joins, and if the optimizer is taking too much time in evaluating the plans, you can determine the best plan and give it a hint to the query. But make sure that the plan you are suggesting is the best and should work in all cases.
How to do it...
Take the same query where you evaluated the use of the redundant index as an example; it is using intersect(from_date,from_date_2). By passing the optimizer hint (/*+ NO_INDEX_MERGE(s from_date,from_date_2) */), you avoided the use of intersect. You can achieve the same behavior by hinting the optimizer to ignore the from_date_2 index...
