•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. 

•If an IGNORE INDEX  hint names multiple indexes, those indexes are unavailable for Index Merge. 

•The FORCE INDEX  and USE INDEX  hints make only the named indexes to be available for Index Merge. 

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 Optimizer Hints 

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: 

hint_name ([@ query_block_name ] [ strategy  [, strategy ] ...]) 

The syntax refers to these terms: 

• hint_name : These hint names are permitted: 

• SEMIJOIN , NO_SEMIJOIN : Enable or disable the named semijoin strategies. 

• strategy : A semijoin strategy to be enabled or disabled. These strategy names are permitted: DUPSWEEDOUT , FIRSTMATCH , LOOSESCAN , MATERIALIZATION . 

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 . 

