Optimizer Hints 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); SUBQUERY([@ query_block_name ]  strategy ) 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); Examples: Examples: Syntax of hints that affect whether to use subquery materialization or  IN -to- EXISTS  transformations: The hint name is always  SUBQUERY . For  SUBQUERY  hints, these  strategy  values are permitted:  INTOEXISTS ,  MATERIALIZATION . 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: SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ... Example with a timeout of 1 second (1000 milliseconds): 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. •It does not apply to  SELECT  statements in stored programs and is ignored. MAX_EXECUTION_TIME( N ) Variable-Setting Hint Syntax 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; 1803