Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.9Mb
PDF (A4) - 30.9Mb
PDF (RPM) - 29.7Mb
HTML Download (TGZ) - 7.7Mb
HTML Download (Zip) - 7.7Mb
HTML Download (RPM) - 6.6Mb
Man Pages (TGZ) - 142.3Kb
Man Pages (Zip) - 201.5Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb


Pre-General Availability Draft: 2017-04-29

9.9.2 Optimizer Hints

One means of control over optimizer strategies is to set the optimizer_switch system variable (see Section 9.9.3, “Switchable Optimizations”). Changes to this variable affect execution of all subsequent queries; to affect one query differently from another, it's necessary to change optimizer_switch before each one.

Another way to control the optimizer is by using optimizer hints, which can be specified within individual statements. Because optimizer hints apply on a per-statement basis, they provide finer control over statement execution plans than can be achieved using optimizer_switch. For example, you can enable an optimization for one table in a statement and disable the optimization for a different table. Hints within a statement take precedence over optimizer_switch flags.

Examples:

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

Optimizer hints, described here, differ from index hints, described in Section 9.9.4, “Index Hints”. Optimizer and index hints may be used separately or together.

Optimizer Hint Overview

Optimizer hints apply at different scope levels:

  • Global: The hint affects the entire statement

  • Query block: The hint affects a particular query block within a statement

  • Table-level: The hint affects a particular table within a query block

  • Index-level: The hint affects a particular index within a table

The following table summarizes the available optimizer hints, the optimizer strategies they affect, and the scope or scopes at which they apply. More details are given later.

Table 9.2 Optimizer Hints Available

Hint Name Description Applicable Scopes
BKA, NO_BKA Affects Batched Key Access join processing Query block, table
BNL, NO_BNL Affects Block Nested-Loop join processing Query block, table
INDEX_MERGE, NO_INDEX_MERGE Affects Index Merge optimization Table, index
JOIN_FIXED_ORDER Use table order specified in FROM clause for join order Query block
JOIN_ORDER Use table order specified in hint for join order Query block
JOIN_PREFIX Use table order specified in hint for first tables of join order Query block
JOIN_SUFFIX Use table order specified in hint for last tables of join order Query block
MAX_EXECUTION_TIME Limits statement execution time Global
MERGE, NO_MERGE Affects derived table/view merging into outer query block Table
MRR, NO_MRR Affects Multi-Range Read optimization Table, index
NO_ICP Affects Index Condition Pushdown optimization Table, index
NO_RANGE_OPTIMIZATION Affects range optimization Table, index
QB_NAME Assigns name to query block Query block
SEMIJOIN, NO_SEMIJOIN Affects semi-join strategies Query block
SUBQUERY Affects materialization, IN-to-EXISTS subquery stratgies Query block

Disabling an optimization prevents the optimizer from using it. Enabling an optimization means the optimizer is free to use the strategy if it applies to statement execution, not that the optimizer necessarily will use it.

Optimizer Hint Syntax

MySQL supports comments in SQL statements as described in Section 10.6, “Comment Syntax”. Optimizer hints must be specified within /*+ ... */ comments. That is, optimizer hints use a variant of /* ... */ C-style comment syntax, with a + character following the /* comment opening sequence. Examples:

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */

Whitespace is permitted after the + character.

The parser recognizes optimizer hint comments after the initial keyword of SELECT, UPDATE, INSERT, REPLACE, and DELETE statements. Hints are permitted in these contexts:

  • At the beginning of query and data change statements:

    SELECT /*+ ... */ ...
    INSERT /*+ ... */ ...
    REPLACE /*+ ... */ ...
    UPDATE /*+ ... */ ...
    DELETE /*+ ... */ ...
    
  • At the beginning of query blocks:

    (SELECT /*+ ... */ ... )
    (SELECT ... ) UNION (SELECT /*+ ... */ ... )
    (SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
    UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
    INSERT ... SELECT /*+ ... */ ...
    
  • In hintable statements prefaced by EXPLAIN. For example:

    EXPLAIN SELECT /*+ ... */ ...
    EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
    

    The implication is that you can use EXPLAIN to see how optimizer hints affect execution plans. Use SHOW WARNINGS immediately after EXPLAIN to see how hints are used. The extended EXPLAIN output displayed by a following SHOW WARNINGS indicates which hints were used. Ignored hints are not displayed.

A hint comment may contain multiple hints, but a query block cannot contain multiple hint comments. This is valid:

SELECT /*+ BNL(t1) BKA(t2) */ ...

But this is invalid:

SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

When a hint comment contains multiple hints, the possibility of duplicates and conflicts exists. The following general guidelines apply. For specific hint types, additional rules may apply, as indicated in the hint descriptions.

  • Duplicate hints: For a hint such as /*+ MRR(idx1) MRR(idx1) */, MySQL uses the first hint and issues a warning about the duplicate hint.

  • Conflicting hints: For a hint such as /*+ MRR(idx1) NO_MRR(idx1) */, MySQL uses the first hint and issues a warning about the second conflicting hint.

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (see Section 10.2, “Schema Object Names”).

Hint names, query block names, and strategy names are not case sensitive. References to table and index names follow the usual identifier case sensitivity rules (see Section 10.2.2, “Identifier Case Sensitivity”).

Join-Order Optimizer Hints

Join-order hints affect the order in which the optimizer joins tables.

Syntax of the JOIN_FIXED_ORDER hint:

hint_name([@query_block_name])

Syntax of other join-order hints:

hint_name([@query_block_name] tbl_name [, tbl_name] ...)
hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)

The syntax refers to these terms:

  • hint_name: These hint names are permitted:

    • JOIN_FIXED_ORDER: Force the optimizer to join tables using the order in which they appear in the FROM clause. This is the same as specifying SELECT STRAIGHT_JOIN.

    • JOIN_ORDER: Instruct the optimizer to join tables using the specified table order. The hint applies to the named tables. The optimizer may place tables that are not named anywhere in the join order, including between specified tables.

    • JOIN_PREFIX: Instruct the optimizer to join tables using the specified table order for the first tables of the join execution plan. The hint applies to the named tables. The optimizer places all other tables after the named tables.

    • JOIN_SUFFIX: Instruct the optimizer to join tables using the specified table order for the last tables of the join execution plan. The hint applies to the named tables. The optimizer places all other tables before the named tables.

  • tbl_name: The name of a table used in the statement. A hint that names tables applies to all tables that it names. The JOIN_FIXED_ORDER hint names no tables and applies to all tables in the FROM clause 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.

Example:

SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
    JOIN_ORDER(t4@subq1, t3) 
    JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
           WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
             AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);

Hints control the behavior of semi-join tables that are merged to the outer query block. If subqueries subq1 and subq2 are converted to semi-joins, tables t4@subq1 and t5@subq2 are merged to the outer query block. In this case, the hint specified in the outer query block controls the behavior of t4@subq1, t5@subq2 tables.

The optimizer resolves join-order hints according to these principles:

  • Multiple hint instances

    Only one JOIN_PREFIX and JOIN_SUFFIX hint of each type are applied. Any later hints of the same type are ignored with a warning. JOIN_ORDER can be specified several times.

    Examples:

    /*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2) */
    

    The second JOIN_PREFIX hint is ignored with a warning.

    /*+ JOIN_PREFIX(t1) JOIN_SUFFIX(t2) */
    

    Both hints are applicable. No warning occurs.

    /*+ JOIN_ORDER(t1, t2) JOIN_ORDER(t2, t3) */
    

    Both hints are applicable. No warning occurs.

  • Conflicting hints

    In some cases hints can conflict, such as when JOIN_ORDER and JOIN_PREFIX have table orders that are impossible to apply at the same time:

    SELECT /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */ ... FROM t1, t2;
    

    In this case, the first specified hint is applied and subsequent conflicting hints are ignored with no warning. A valid hint that is impossible to apply is silently ignored with no warning.

  • Ignored hints

    A hint is ignored if a table specified in the hint has a circular dependency.

    Example:

    /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */  
    

    The JOIN_ORDER hint sets table t2 dependent on t1. The JOIN_PREFIX hint is ignored because table t1 cannot be dependent on t2. Ignored hints are not displayed in extended EXPLAIN output.

  • Interaction with const tables

    The MySQL optimizer places const tables first in the join order, and the position of a const table cannot be affected by hints. References to const tables in join-order hints are ignored, although the hint is still applicable. For example, these are equivalent:

    JOIN_ORDER(t1, const_tbl, t2)
    JOIN_ORDER(t1, t2)
    

    Accepted hints shown in extended EXPLAIN output include const tables as they were specified.

  • Interaction with types of join operations

    MySQL supports several type of joins: LEFT, RIGHT, INNER, CROSS, STRAIGHT_JOIN. A hint that conflicts with the specified type of join is ignored with no warning.

    Example:

    SELECT /*+ JOIN_PREFIX(t1, t2) */FROM t2 LEFT JOIN t1; 
    

    Here a conflict occurs between the requested join order in the hint and the order required by the LEFT JOIN. The hint is ignored with no warning.

Table-Level Optimizer Hints

Table-level hints affect:

These hint types apply to specific tables, or all tables in a query block.

Syntax of table-level hints:

hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
hint_name([tbl_name@query_block_name [, tbl_name@query_block_name] ...])

The syntax refers to these terms:

  • hint_name: These hint names are permitted:

    • BKA, NO_BKA: Enable or disable BKA for the specified tables.

    • BNL, NO_BNL: Enable or disable BNL for the specified tables.

    • MERGE, NO_MERGE: Enable merging for the specified tables, view references or common table expressions; or disable merging and use materialization instead.

    Note

    To use a BNL or BKA 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.

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

A table-level hint applies to tables that receive records from previous tables, not sender tables. Consider this statement:

SELECT /*+ BNL(t2) */ FROM t1, t2;

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 9.2.1, “Optimizing SELECT Statements”).

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] ...])

The syntax refers to these terms:

  • hint_name: These hint names are permitted:

    • INDEX_MERGE, NO_INDEX_MERGE: Enable or disable the Index Merge access method for the specified tables or indexes. For information about this access method, see Section 9.2.1.3, “Index Merge Optimization”. These hints apply to all three Index Merge algorithms.

      The INDEX_MERGE hint forces the optimizer to use Index Merge for the specified table using the specified set of indexes. If no index is specified, the optimizer considers all possible index combinations and selects the least expensive one. The hint may be ignored if the index combination is inapplicable to the given statement.

      The NO_INDEX_MERGE hint disables Index Merge combinations that involve any of the specified indexes. If the hint specifies no indexes, Index Merge is not permitted for the table.

    • MRR, NO_MRR: Enable or disable MRR for the specified tables or indexes. MRR hints apply only to InnoDB and MyISAM tables.

    • NO_ICP: Disable ICP for the specified tables or indexes. By default, ICP is a candidate optimization strategy, so there is no hint for enabling it.

    • NO_RANGE_OPTIMIZATION: Disable index range access for the specified tables or indexes. This hint also disables Index Merge and Loose Index Scan for the tables or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it.

      This hint may be useful when the number of ranges may be high and range optimization would require many resources.

  • 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);

Index Merge examples. 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
    WHERE b = 1 AND c = 2 AND d = 3;
    

    The hint takes precedence over optimizer_switch. Index Merge is used for (i_b, i_c) in this case.

    SET optimizer_switch='index_merge_intersection=on';
    SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
    WHERE b = 1 AND c = 2 AND d = 3;
    

    The hint specifies only one index, so it is inapplicable, and the optimizer_switch flag (on) applies. Index Merge is used if the optimizer assesses it to be cost efficient.

    SET optimizer_switch='index_merge_intersection=off';
    SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
    WHERE b = 1 AND c = 2 AND d = 3;
    

    The hint specifies only one index, so it is inapplicable, and the optimizer_switch flag (off) applies. Index Merge is not used.

  • The USE INDEX, FORCE INDEX, and IGNORE INDEX index hints have higher priority than the INDEX_MERGE and NO_INDEX_MERGE optimizer hints.

    /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ ... IGNORE INDEX i_a
    

    IGNORE INDEX takes precedence over INDEX_MERGE, so index i_a is excluded from the possible ranges for Index Merge.

    /*+ NO_INDEX_MERGE(t1 i_a, i_b) */ ... FORCE INDEX i_a, i_b
    

    Index Merge is disallowed for i_a, i_b because of FORCE INDEX, but the optimizer is forced to use either i_a or i_b for range or ref access. There are no conflicts; both hints are applicable.

  • If an IGNORE INDEX hint names multiple indexes, those indexes are unavailable for Index Merge.

  • The FORCE INDEX and USE INDEX hints make only the named indexes to be available for Index Merge.

    SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ a FROM t1
    FORCE INDEX (i_a, i_b) WHERE c = 'h' AND a = 2 AND b = 'b';
    

    The Index Merge intersection access algorithm is used for (i_a, i_b). The same is true if FORCE INDEX is changed to USE INDEX.

Subquery Optimizer Hints

Subquery hints affect whether to use semi-join transformations and which semi-join strategies to permit, and, when semi-joins are not used, whether to use subquery materialization or IN-to-EXISTS transformations. For more information about these optimizations, see Section 9.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”.

Syntax of hints that affect semi-join strategies:

hint_name([@query_block_name] [strategy [, strategy] ...])

The syntax refers to these terms:

  • hint_name: These hint names are permitted:

    • SEMIJOIN, NO_SEMIJOIN: Enable or disable the named semi-join strategies.

  • strategy: A semi-join strategy to be enabled or disabled. These strategy names are permitted: DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION.

    For SEMIJOIN hints, if no strategies are named, semi-join is used if possible based on the strategies enabled according to the optimizer_switch system variable. If strategies are named but inapplicable for the statement, DUPSWEEDOUT is used.

    For NO_SEMIJOIN hints, if no strategies are named, semi-join is not used. If strategies are named that rule out all applicable strategies for the statement, DUPSWEEDOUT is used.

If one subquery is nested within another and both are merged into a semi-join of an outer query, any specification of semi-join strategies for the innermost query are ignored. SEMIJOIN and NO_SEMIJOIN hints can still be used to enable or disable semi-join transformations for such nested subqueries.

If DUPSWEEDOUT is disabled, on occasion the optimizer may generate a query plan that is far from optimal. This occurs due to heuristic pruning during greedy search, which can be avoided by setting optimizer_prune_level=0.

Examples:

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.

Examples:

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 semi-join 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.

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

Optimizer Hints for Naming Query Blocks

Table-level, index-level, and subquery optimizer hints permit specific query blocks to be named as part of their argument syntax. To create these names, use the QB_NAME hint, which assigns a name to the query block in which it occurs:

QB_NAME(name)

QB_NAME hints can be used to make explicit in a clear way which query blocks other hints apply to. They also permit all non-query block name hints to be specified within a single hint comment for easier understanding of complex statements. Consider the following statement:

SELECT ...
  FROM (SELECT ...
  FROM (SELECT ... FROM ...)) ...

QB_NAME hints assign names to query blocks in the statement:

SELECT /*+ QB_NAME(qb1) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

Then other hints can use those names to refer to the appropriate query blocks:

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

The resulting effect is as follows:

  • MRR(@qb1 t1) applies to table t1 in query block qb1.

  • BKA(@qb2) applies to query block qb2.

  • NO_MRR(@qb3 t1 idx1, id2) applies to indexes idx1 and idx2 in table t1 in query block qb3.

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (see Section 10.2, “Schema Object Names”). For example, a query block name that contains spaces must be quoted, which can be done using backticks:

SELECT /*+ BKA(@`my hint name`) */ ...
  FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

If the ANSI_QUOTES SQL mode is enabled, it is also possible to quote query block names within double quotation marks:

SELECT /*+ BKA(@"my hint name") */ ...
  FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...

User Comments
Sign Up Login You must be logged in to post a comment.