Optimizer Hints To use a block nested loop or batched key access hint to enable join buffering for any inner table of an outer join, join buffering must be enabled for all inner tables of the outer join. Note • tbl_name : The name of a table used in the statement. The hint applies to all tables that it names. If the hint names no tables, it applies to all tables of the query block in which it occurs. If a table has an alias, hints must refer to the alias, not the table name. Table names in hints cannot be qualified with schema names. • 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 /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3; SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3; SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt; SELECT /*+ BNL(t2) */ FROM t1, t2; A table-level hint applies to tables that receive records from previous tables, not sender tables. Consider this statement: If the optimizer chooses to process  t1  first, it applies a Block Nested-Loop join to  t2  by buffering the rows from  t1  before starting to read from  t2 . If the optimizer instead chooses to process  t2  first, the hint has no effect because  t2  is a sender table. For the  MERGE  and  NO_MERGE  hints, these precedence rules apply: •A hint takes precedence over any optimizer heuristic that is not a technical constraint. (If providing a hint as a suggestion has no effect, the optimizer has a reason for ignoring it.) •A hint takes precedence over the  derived_merge  flag of the  optimizer_switch  system variable. •For view references, an  ALGORITHM={MERGE|TEMPTABLE}  clause in the view definition takes precedence over a hint specified in the query referencing the view. Index-Level Optimizer Hints Index-level hints affect which index-processing strategies the optimizer uses for particular tables or indexes. These hint types affect use of Index Condition Pushdown (ICP), Multi-Range Read (MRR), Index Merge, and range optimizations (see Section 8.2.1, “Optimizing SELECT Statements” ). The syntax refers to these terms: • hint_name : These hint names are permitted: Syntax of index-level hints: hint_name ([@ query_block_name ]  tbl_name  [ index_name  [,  index_name ] ...]) hint_name ( tbl_name @ query_block_name  [ index_name  [,  index_name ] ...]) 1798