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

To refer to a primary key, use the name PRIMARY . To see the index names for a table, use SHOW INDEX . 

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

Examples: 

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; 

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. 

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: 

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1 WHERE a = 1 AND b = 2 AND c = 3 AND d = 4; 

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 b = 1 AND c = 2 AND d = 3; 

Index Merge is used for (i_b, i_c)  in this case. 

/*+ INDEX_MERGE(t1 i_a, i_b) NO_INDEX_MERGE(t1 i_b) */ 

NO_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) */ 

INDEX_MERGE  is ignored because there is a preceding hint for the same table. 

For the INDEX_MERGE  and NO_INDEX_MERGE  optimizer hints, these precedence rules apply: 

•If an optimizer hint is specified and is applicable, it takes precedence over the Index Merge-related flags of the optimizer_switch  system variable. 

SET optimizer_switch=’index_merge_intersection=off’; SELECT /*+ INDEX_MERGE(t1 i_b, i_c) */ * FROM t1 

