Optimizer Hints • tbl_name : The table to which the hint applies. • index_name : The name of an index in the named table. The hint applies to all indexes that it names. If the hint names no indexes, it applies to all indexes in the table. • query_block_name : The query block to which the hint applies. If the hint includes no leading  @ query_block_name , the hint applies to the query block in which it occurs. For tbl_name @ query_block_name  syntax, the hint applies to the named table in the named query block. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks. 1800 SET optimizer_switch=’index_merge_intersection=off’; SELECT /*+ INDEX_MERGE(t1 i_b, i_c) */ * FROM t1 •If an optimizer hint is specified and is applicable, it takes precedence over the Index Merge-related flags of the  optimizer_switch  system variable. For the  INDEX_MERGE  and  NO_INDEX_MERGE  optimizer hints, these precedence rules apply: INDEX_MERGE  is ignored because there is a preceding hint for the same table. /*+ NO_INDEX_MERGE(t1 i_a, i_b) INDEX_MERGE(t1 i_b) */ NO_INDEX_MERGE  is ignored because there is a preceding hint for the same table. /*+ INDEX_MERGE(t1 i_a, i_b) NO_INDEX_MERGE(t1 i_b) */ Index Merge is used for  (i_b, i_c)  in this case. SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1 WHERE b = 1 AND c = 2 AND d = 3; Index Merge is used for  (i_a, i_b, i_c)  in this case. SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1 WHERE a = 1 AND b = 2 AND c = 3 AND d = 4; Assume that table  t1  has columns  a ,  b ,  c , and  d ; and that indexes named  i_a ,  i_b , and  i_c  exist on  a , b , and  c , respectively: The following examples use the Index Merge hints, but other index-level hints follow the same principles regarding hint ignoring and precedence of optimizer hints in relation to the  optimizer_switch  system variable or index hints. Examples: To refer to a primary key, use the name  PRIMARY . To see the index names for a table, use  SHOW INDEX . SELECT /*+ INDEX_MERGE(t1 f3, PRIMARY) */ f2 FROM t1 WHERE f1 = ’o’ AND f2 = f3 AND f3 <= 4; SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3; SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33; INSERT INTO t3(f1, f2, f3) (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1); SELECT /*+ SKIP_SCAN(t1 PRIMARY) */ f1, f2 FROM t1 WHERE f2 > 40;