Optimizer Hints •The  USE INDEX ,  FORCE INDEX , and  IGNORE INDEX  index hints have higher priority than the INDEX_MERGE  and  NO_INDEX_MERGE  optimizer hints. /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ ... IGNORE INDEX i_a IGNORE INDEX  takes precedence over  INDEX_MERGE , so index  i_a  is excluded from the possible ranges for Index Merge. /*+ NO_INDEX_MERGE(t1 i_a, i_b) */ ... FORCE INDEX i_a, i_b Index Merge is disallowed for  i_a, i_b  because of  FORCE INDEX , but the optimizer is forced to use either  i_a  or  i_b  for  range  or  ref  access. There are no conflicts; both hints are applicable. SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ a FROM t1 FORCE INDEX (i_a, i_b) WHERE c = ’h’ AND a = 2 AND b = ’b’; The Index Merge intersection access algorithm is used for  (i_a, i_b) . The same is true if  FORCE INDEX  is changed to  USE INDEX . Subquery hints affect whether to use semijoin transformations and which semijoin strategies to permit, and, when semijoins are not used, whether to use subquery materialization or  IN -to- EXISTS  transformations. For more information about these optimizations, see Section 8.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions” . Syntax of hints that affect semijoin strategies: The syntax refers to these terms: For  SEMIJOIN  hints, if no strategies are named, semijoin is used if possible based on the strategies enabled according to the  optimizer_switch  system variable. If strategies are named but inapplicable for the statement,  DUPSWEEDOUT  is used. For  NO_SEMIJOIN  hints, if no strategies are named, semijoin is not used. If strategies are named that rule out all applicable strategies for the statement,  DUPSWEEDOUT  is used. If one subquery is nested within another and both are merged into a semijoin of an outer query, any specification of semijoin strategies for the innermost query are ignored.  SEMIJOIN  and  NO_SEMIJOIN hints can still be used to enable or disable semijoin transformations for such nested subqueries. If  DUPSWEEDOUT  is disabled, on occasion the optimizer may generate a query plan that is far from optimal. This occurs due to heuristic pruning during greedy search, which can be avoided by setting optimizer_prune_level=0 . 1802 • strategy : A semijoin strategy to be enabled or disabled. These strategy names are permitted: DUPSWEEDOUT ,  FIRSTMATCH ,  LOOSESCAN ,  MATERIALIZATION . • SEMIJOIN ,  NO_SEMIJOIN : Enable or disable the named semijoin strategies. • hint_name : These hint names are permitted: •The  FORCE INDEX  and  USE INDEX  hints make only the named indexes to be available for Index Merge. •If an  IGNORE INDEX  hint names multiple indexes, those indexes are unavailable for Index Merge. Subquery Optimizer Hints hint_name ([@ query_block_name ] [ strategy  [,  strategy ] ...])