SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 

Syntax of hints that affect whether to use subquery materialization or IN -to- EXISTS  transformations: 

SUBQUERY([@ query_block_name ] strategy ) 

The hint name is always SUBQUERY . 

For SUBQUERY  hints, these strategy  values are permitted: INTOEXISTS , MATERIALIZATION . 

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2; SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1); 

For semijoin and SUBQUERY  hints, a leading @ query_block_name  specifies 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. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks. 

If a hint comment contains multiple subquery hints, the first is used. If there are other following hints of that type, they produce a warning. Following hints of other types are silently ignored. 

## Statement Execution Time Optimizer Hints 

The MAX_EXECUTION_TIME  hint is permitted only for SELECT  statements. It places a limit N  (a timeout value in milliseconds) on how long a statement is permitted to execute before the server terminates it: 

MAX_EXECUTION_TIME( N ) 

Example with a timeout of 1 second (1000 milliseconds): 

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ... 

The MAX_EXECUTION_TIME( N )  hint sets a statement execution timeout of N  milliseconds. If this option is absent or N  is 0, the statement timeout established by the max_execution_time  system variable applies. 

The MAX_EXECUTION_TIME  hint is applicable as follows: 

•For statements with multiple SELECT  keywords, such as unions or statements with subqueries, MAX_EXECUTION_TIME  applies to the entire statement and must appear after the first SELECT . 

•It applies to read-only SELECT  statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect. 

## Variable-Setting Hint Syntax 

•It does not apply to SELECT  statements in stored programs and is ignored. 

The SET_VAR  hint sets the session value of a system variable temporarily (for the duration of a single statement). Examples: 

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name; INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2); SELECT /*+ SET_VAR(optimizer_switch = ’mrr_cost_based=off’) */ 1; 

