MySQL :: MySQL 8.4 Reference Manual :: 10.9.3 Optimizer Hints (original) (raw)

10.9.3 Optimizer Hints

One means of control over optimizer strategies is to set theoptimizer_switch system variable (see Section 10.9.2, “Switchable Optimizations”). Changes to this variable affect execution of all subsequent queries; to affect one query differently from another, it is necessary to changeoptimizer_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 usingoptimizer_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 overoptimizer_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;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

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

Optimizer Hint Overview

Optimizer hints apply at different scope levels:

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 10.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 hash join optimization Query block, table
DERIVED_CONDITION_PUSHDOWN,NO_DERIVED_CONDITION_PUSHDOWN Use or ignore the derived condition pushdown optimization for materialized derived tables Query block, table
GROUP_INDEX,NO_GROUP_INDEX Use or ignore the specified index or indexes for index scans inGROUP BY operations Index
HASH_JOIN,NO_HASH_JOIN Affects Hash Join optimization (No effect in MySQL 8.4) Query block, table
INDEX,NO_INDEX Acts as the combination of JOIN_INDEX,GROUP_INDEX, andORDER_INDEX, or as the combination ofNO_JOIN_INDEX,NO_GROUP_INDEX, andNO_ORDER_INDEX Index
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_INDEX,NO_JOIN_INDEX Use or ignore the specified index or indexes for any access method Index
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
ORDER_INDEX,NO_ORDER_INDEX Use or ignore the specified index or indexes for sorting rows Index
QB_NAME Assigns name to query block Query block
RESOURCE_GROUP Set resource group during statement execution Global
SEMIJOIN,NO_SEMIJOIN Affects semijoin and antijoin strategies Query block
SKIP_SCAN,NO_SKIP_SCAN Affects Skip Scan optimization Table, index
SET_VAR Set variable during statement execution Global
SUBQUERY Affects materialization,IN-to-EXISTS subquery strategies 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 uses it.

Optimizer Hint Syntax

MySQL supports comments in SQL statements as described inSection 11.7, “Comments”. 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, andDELETE statements. Hints are permitted in these contexts:

SELECT /*+ ... */ ...  
INSERT /*+ ... */ ...  
REPLACE /*+ ... */ ...  
UPDATE /*+ ... */ ...  
DELETE /*+ ... */ ...  
(SELECT /*+ ... */ ... )  
(SELECT ... ) UNION (SELECT /*+ ... */ ... )  
(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )  
UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)  
INSERT ... SELECT /*+ ... */ ...  
EXPLAIN SELECT /*+ ... */ ...  
EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

The implication is that you can useEXPLAIN to see how optimizer hints affect execution plans. UseSHOW 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.

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (seeSection 11.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 (seeSection 11.2.3, “Identifier Case Sensitivity”).

Join-Order Optimizer Hints

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

Syntax of theJOIN_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:

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 semijoin tables that are merged to the outer query block. If subqueriessubq1 and subq2 are converted to semijoins, tables t4@subq1 andt5@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:

/*+ 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.

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.

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

The JOIN_ORDER hint sets table t2 dependent ont1. TheJOIN_PREFIX hint is ignored because table t1 cannot be dependent on t2. Ignored hints are not displayed in extendedEXPLAIN output.

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

Accepted hints shown in extendedEXPLAIN output includeconst tables as they were specified.

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:

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 tot2 by buffering the rows fromt1 before starting to read fromt2. If the optimizer instead chooses to process t2 first, the hint has no effect because t2 is a sender table.

For the MERGE andNO_MERGE hints, these precedence rules apply:

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 10.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:

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 theoptimizer_switch system variable or index hints.

Assume that table t1 has columnsa, 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 andNO_INDEX_MERGE optimizer hints, these precedence rules apply:

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

SELECT /*+ NO_ORDER_INDEX(t1 i_b,i_c) */ a,c  
    FROM t1  
    ORDER BY a;

Attempting to combine NO_ORDER_INDEX for the table as a whole with USE INDEX FOR ORDER BY does not work to do this, becauseNO_ORDER_BY causes USE INDEX to be ignored, as shown here: 

mysql> EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1) */ a,c FROM t1  
    ->     USE INDEX FOR ORDER BY (i_a) ORDER BY a\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: t1  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 256  
     filtered: 100.00  
        Extra: Using filesort  
/*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ ... IGNORE INDEX i_a

IGNORE INDEX takes precedence overINDEX_MERGE, so indexi_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 forrange orref access. There are no conflicts; both hints are applicable.

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 ifFORCE INDEX is changed to USE INDEX.

Subquery Optimizer Hints

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

Syntax of hints that affect semijoin strategies:

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

The syntax refers to these terms:

If one subquery is nested within another and both are merged into a semijoin of an outer query, any specification of semijoin strategies for the innermost query are ignored.SEMIJOIN andNO_SEMIJOIN hints can still be used to enable or disable semijoin 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 settingoptimizer_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 orIN-to-EXISTS transformations:

SUBQUERY([@query_block_name] strategy)

The hint name is alwaysSUBQUERY.

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 semijoin and SUBQUERY hints, a leading@_`queryblockname`_ specifies the query block to which the hint applies. If the hint includes no leading@_`queryblockname`_, the hint applies to the query block in which it occurs. To assign a name to a query block, seeOptimizer 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 ...

TheMAX_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 themax_execution_time system variable applies.

The MAX_EXECUTION_TIME hint is applicable as follows:

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;

Syntax of the SET_VAR hint:

SET_VAR(var_name = value)

varname names a system variable that has a session value (although not all such variables can be named, as explained later).value is the value to assign to the variable; the value must be a scalar.

SET_VAR makes a temporary variable change, as demonstrated by these statements:

mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+
mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               0 |
+-----------------+
mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+

With SET_VAR, there is no need to save and restore the variable value. This enables you to replace multiple statements by a single statement. Consider this sequence of statements:

SET @saved_val = @@SESSION.var_name;
SET @@SESSION.var_name = value;
SELECT ...
SET @@SESSION.var_name = @saved_val;

The sequence can be replaced by this single statement:

SELECT /*+ SET_VAR(var_name = value) ...

StandaloneSET statements permit any of these syntaxes for naming session variables:

SET SESSION var_name = value;
SET @@SESSION.var_name = value;
SET @@.var_name = value;

Because the SET_VAR hint applies only to session variables, session scope is implicit, and SESSION, @@SESSION., and @@ are neither needed nor permitted. Including explicit session-indicator syntax results in theSET_VAR hint being ignored with a warning.

Not all session variables are permitted for use withSET_VAR. Individual system variable descriptions indicate whether each variable is hintable; see Section 7.1.8, “Server System Variables”. You can also check a system variable at runtime by attempting to use it with SET_VAR. If the variable is not hintable, a warning occurs:

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4537
Message: Variable 'collation_server' cannot be set using SET_VAR hint.

SET_VAR syntax permits setting only a single variable, but multiple hints can be given to set multiple variables:

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off')
           SET_VAR(max_heap_table_size = 1G) */ 1;

If several hints with the same variable name appear in the same statement, the first one is applied and the others are ignored with a warning:

SELECT /*+ SET_VAR(max_heap_table_size = 1G)
           SET_VAR(max_heap_table_size = 3G) */ 1;

In this case, the second hint is ignored with a warning that it is conflicting.

A SET_VAR hint is ignored with a warning if no system variable has the specified name or the variable value is incorrect:

SELECT /*+ SET_VAR(max_size = 1G) */ 1;
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;

For the first statement, there is nomax_size variable. For the second statement, mrr_cost_based takes values of on oroff, so attempting to set it toyes is incorrect. In each case, the hint is ignored with a warning.

The SET_VAR hint is permitted only at the statement level. If used in a subquery, the hint is ignored with a warning.

Replicas ignore SET_VAR hints in replicated statements to avoid the potential for security issues.

Resource Group Hint Syntax

The RESOURCE_GROUP optimizer hint is used for resource group management (seeSection 7.1.16, “Resource Groups”). This hint assigns the thread that executes a statement to the named resource group temporarily (for the duration of the statement). It requires the RESOURCE_GROUP_ADMIN orRESOURCE_GROUP_USER privilege.

Examples:

SELECT /*+ RESOURCE_GROUP(USR_default) */ name FROM people ORDER BY name;
INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);

Syntax of the RESOURCE_GROUP hint:

RESOURCE_GROUP(group_name)

groupname indicates the resource group to which the thread should be assigned for the duration of statement execution. If the group is nonexistent, a warning occurs and the hint is ignored.

The RESOURCE_GROUP hint must appear after the initial statement keyword (SELECT, INSERT,REPLACE, UPDATE, orDELETE).

An alternative toRESOURCE_GROUP is theSET RESOURCE GROUP statement, which nontemporarily assigns threads to a resource group. SeeSection 15.7.2.4, “SET RESOURCE GROUP Statement”.

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 theQB_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:

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (seeSection 11.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") */ ...) ...