<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizer-hints">
      </a>
      10.9.3 Optimizer Hints
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045224183312">
  </a>
  <a class="indexterm" name="idm46045224182240">
  </a>
  <p>
   One means of control over optimizer strategies is to set the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system
        variable (see
   <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
    Section 10.9.2, “Switchable Optimizations”
   </a>
   ).
        Changes to this variable affect execution of all subsequent
        queries; to affect one query differently from another, it is
        necessary to change
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   before each
        one.
  </p>
  <p>
   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
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   . 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
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   flags.
  </p>
  <p>
   Examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33736983"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */</span> f1
  <span class="token keyword">FROM</span> t3 <span class="token keyword">WHERE</span> f1 <span class="token operator">&gt;</span> <span class="token number">30</span> <span class="token operator">AND</span> f1 <span class="token operator">&lt;</span> <span class="token number">33</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ BKA(t1) NO_BKA(t2) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_ICP(t1, t2) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_ICP(t1) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ MERGE(dt) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span> <span class="token keyword">AS</span> dt<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(foreign_key_checks=OFF) */</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Optimizer hints, described here, differ from index hints,
        described in
   <a class="xref" href="index-hints.html" title="10.9.4 Index Hints">
    Section 10.9.4, “Index Hints”
   </a>
   . Optimizer and index
        hints may be used separately or together.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-overview" title="Optimizer Hint Overview">
       Optimizer Hint Overview
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-syntax" title="Optimizer Hint Syntax">
       Optimizer Hint Syntax
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
       Join-Order Optimizer Hints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
       Table-Level Optimizer Hints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
       Index-Level Optimizer Hints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
       Subquery Optimizer Hints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-execution-time" title="Statement Execution Time Optimizer Hints">
       Statement Execution Time Optimizer Hints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
       Variable-Setting Hint Syntax
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-resource-group" title="Resource Group Hint Syntax">
       Resource Group Hint Syntax
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
       Optimizer Hints for Naming Query Blocks
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-overview">
       </a>
       Optimizer Hint Overview
      </h4>
     </div>
    </div>
   </div>
   <p>
    Optimizer hints apply at different scope levels:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Global: The hint affects the entire statement
      </p>
     </li>
     <li class="listitem">
      <p>
       Query block: The hint affects a particular query block
              within a statement
      </p>
     </li>
     <li class="listitem">
      <p>
       Table-level: The hint affects a particular table within a
              query block
      </p>
     </li>
     <li class="listitem">
      <p>
       Index-level: The hint affects a particular index within a
              table
      </p>
     </li>
    </ul>
   </div>
   <p>
    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.
   </p>
   <div class="table">
    <a name="optimizer-hints-table">
    </a>
    <p class="title">
     <b>
      Table 10.2 Optimizer Hints Available
     </b>
    </p>
    <div class="table-contents">
     <table summary="Optimizer hint names, descriptions, and contexts in which they apply.">
      <colgroup>
       <col style="width: 30%"/>
       <col style="width: 40%"/>
       <col style="width: 30%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Hint Name
        </th>
        <th scope="col">
         Description
        </th>
        <th scope="col">
         Applicable Scopes
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           BKA
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           NO_BKA
          </code>
         </a>
        </th>
        <td>
         Affects Batched Key Access join processing
        </td>
        <td>
         Query block, table
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           BNL
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           NO_BNL
          </code>
         </a>
        </th>
        <td>
         Affects hash join optimization
        </td>
        <td>
         Query block, table
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           DERIVED_CONDITION_PUSHDOWN
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           NO_DERIVED_CONDITION_PUSHDOWN
          </code>
         </a>
        </th>
        <td>
         Use or ignore the derived condition pushdown optimization for
                materialized derived tables
        </td>
        <td>
         Query block, table
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           GROUP_INDEX
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_GROUP_INDEX
          </code>
         </a>
        </th>
        <td>
         Use or ignore the specified index or indexes for index scans in
         <code class="literal">
          GROUP BY
         </code>
         operations
        </td>
        <td>
         Index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           HASH_JOIN
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           NO_HASH_JOIN
          </code>
         </a>
        </th>
        <td>
         Affects Hash Join optimization (No effect in MySQL 8.4)
        </td>
        <td>
         Query block, table
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           INDEX
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_INDEX
          </code>
         </a>
        </th>
        <td>
         Acts as the combination of
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           JOIN_INDEX
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           GROUP_INDEX
          </code>
         </a>
         , and
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           ORDER_INDEX
          </code>
         </a>
         , or as the
                combination of
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_JOIN_INDEX
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_GROUP_INDEX
          </code>
         </a>
         , and
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_ORDER_INDEX
          </code>
         </a>
        </td>
        <td>
         Index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           INDEX_MERGE
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_INDEX_MERGE
          </code>
         </a>
        </th>
        <td>
         Affects Index Merge optimization
        </td>
        <td>
         Table, index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
          <code class="literal">
           JOIN_FIXED_ORDER
          </code>
         </a>
        </th>
        <td>
         Use table order specified in
         <code class="literal">
          FROM
         </code>
         clause for join
                order
        </td>
        <td>
         Query block
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           JOIN_INDEX
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_JOIN_INDEX
          </code>
         </a>
        </th>
        <td>
         Use or ignore the specified index or indexes for any access method
        </td>
        <td>
         Index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
          <code class="literal">
           JOIN_ORDER
          </code>
         </a>
        </th>
        <td>
         Use table order specified in hint for join order
        </td>
        <td>
         Query block
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
          <code class="literal">
           JOIN_PREFIX
          </code>
         </a>
        </th>
        <td>
         Use table order specified in hint for first tables of join order
        </td>
        <td>
         Query block
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
          <code class="literal">
           JOIN_SUFFIX
          </code>
         </a>
        </th>
        <td>
         Use table order specified in hint for last tables of join order
        </td>
        <td>
         Query block
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-execution-time" title="Statement Execution Time Optimizer Hints">
          <code class="literal">
           MAX_EXECUTION_TIME
          </code>
         </a>
        </th>
        <td>
         Limits statement execution time
        </td>
        <td>
         Global
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           MERGE
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
          <code class="literal">
           NO_MERGE
          </code>
         </a>
        </th>
        <td>
         Affects derived table/view merging into outer query block
        </td>
        <td>
         Table
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           MRR
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_MRR
          </code>
         </a>
        </th>
        <td>
         Affects Multi-Range Read optimization
        </td>
        <td>
         Table, index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_ICP
          </code>
         </a>
        </th>
        <td>
         Affects Index Condition Pushdown optimization
        </td>
        <td>
         Table, index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_RANGE_OPTIMIZATION
          </code>
         </a>
        </th>
        <td>
         Affects range optimization
        </td>
        <td>
         Table, index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           ORDER_INDEX
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_ORDER_INDEX
          </code>
         </a>
        </th>
        <td>
         Use or ignore the specified index or indexes for sorting rows
        </td>
        <td>
         Index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
          <code class="literal">
           QB_NAME
          </code>
         </a>
        </th>
        <td>
         Assigns name to query block
        </td>
        <td>
         Query block
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-resource-group" title="Resource Group Hint Syntax">
          <code class="literal">
           RESOURCE_GROUP
          </code>
         </a>
        </th>
        <td>
         Set resource group during statement execution
        </td>
        <td>
         Global
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
          <code class="literal">
           SEMIJOIN
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
          <code class="literal">
           NO_SEMIJOIN
          </code>
         </a>
        </th>
        <td>
         Affects semijoin and antijoin strategies
        </td>
        <td>
         Query block
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           SKIP_SCAN
          </code>
         </a>
         ,
         <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
          <code class="literal">
           NO_SKIP_SCAN
          </code>
         </a>
        </th>
        <td>
         Affects Skip Scan optimization
        </td>
        <td>
         Table, index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
          <code class="literal">
           SET_VAR
          </code>
         </a>
        </th>
        <td>
         Set variable during statement execution
        </td>
        <td>
         Global
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
          <code class="literal">
           SUBQUERY
          </code>
         </a>
        </th>
        <td>
         Affects materialization,
         <code class="literal">
          IN
         </code>
         -to-
         <code class="literal">
          EXISTS
         </code>
         subquery strategies
        </td>
        <td>
         Query block
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 739px;">
      <thead>
       <tr>
        <th scope="col" style="width: 242.641px;">
         Hint Name
        </th>
        <th scope="col" style="width: 283.516px;">
         Description
        </th>
        <th scope="col" style="width: 211.844px;">
         Applicable Scopes
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <p>
    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.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-syntax">
       </a>
       Optimizer Hint Syntax
      </h4>
     </div>
    </div>
   </div>
   <p>
    MySQL supports comments in SQL statements as described in
    <a class="xref" href="comments.html" title="11.7 Comments">
     Section 11.7, “Comments”
    </a>
    . Optimizer hints must be specified
          within
    <code class="literal">
     /*+ ... */
    </code>
    comments. That is,
          optimizer hints use a variant of
    <code class="literal">
     /* ... */
    </code>
    C-style comment syntax, with a
    <code class="literal">
     +
    </code>
    character
          following the
    <code class="literal">
     /*
    </code>
    comment opening sequence.
          Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65833951"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ BKA(t1) */</span>
<span class="token comment" spellcheck="true">/*+ BNL(t1, t2) */</span>
<span class="token comment" spellcheck="true">/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */</span>
<span class="token comment" spellcheck="true">/*+ QB_NAME(qb2) */</span></code></pre>
   </div>
   <p>
    Whitespace is permitted after the
    <code class="literal">
     +
    </code>
    character.
   </p>
   <p>
    The parser recognizes optimizer hint comments after the
          initial keyword of
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    ,
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    ,
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    ,
    <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
     <code class="literal">
      REPLACE
     </code>
    </a>
    , and
    <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
     <code class="literal">
      DELETE
     </code>
    </a>
    statements. Hints are
          permitted in these contexts:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       At the beginning of query and data change statements:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15791451"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">INSERT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">REPLACE</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">UPDATE</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">DELETE</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       At the beginning of query blocks:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa54181279"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">)</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">)</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">)</span>
<span class="token keyword">UPDATE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> x <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
<span class="token keyword">INSERT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       In hintable statements prefaced by
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       . For example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42605398"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">EXPLAIN</span> <span class="token keyword">UPDATE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> x <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ ... */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       The implication is that you can use
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       to see how
              optimizer hints affect execution plans. Use
       <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
        <code class="literal">
         SHOW WARNINGS
        </code>
       </a>
       immediately
              after
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       to see how
              hints are used. The extended
       <code class="literal">
        EXPLAIN
       </code>
       output displayed by a following
       <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
        <code class="literal">
         SHOW
              WARNINGS
        </code>
       </a>
       indicates which hints were used.
              Ignored hints are not displayed.
      </p>
     </li>
    </ul>
   </div>
   <p>
    A hint comment may contain multiple hints, but a query block
          cannot contain multiple hint comments. This is valid:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa23727463"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ BNL(t1) BKA(t2) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    But this is invalid:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa19595700"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ BNL(t1) */</span> <span class="token comment" spellcheck="true">/* BKA(t2) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    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.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Duplicate hints: For a hint such as
       <code class="literal">
        /*+ MRR(idx1)
              MRR(idx1) */
       </code>
       , MySQL uses the first hint and
              issues a warning about the duplicate hint.
      </p>
     </li>
     <li class="listitem">
      <p>
       Conflicting hints: For a hint such as
       <code class="literal">
        /*+
              MRR(idx1) NO_MRR(idx1) */
       </code>
       , MySQL uses the first
              hint and issues a warning about the second conflicting
              hint.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Query block names are identifiers and follow the usual rules
          about what names are valid and how to quote them (see
    <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
     Section 11.2, “Schema Object Names”
    </a>
    ).
   </p>
   <p>
    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
    <a class="xref" href="identifier-case-sensitivity.html" title="11.2.3 Identifier Case Sensitivity">
     Section 11.2.3, “Identifier Case Sensitivity”
    </a>
    ).
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-join-order">
       </a>
       Join-Order Optimizer Hints
      </h4>
     </div>
    </div>
   </div>
   <p>
    Join-order hints affect the order in which the optimizer joins
          tables.
   </p>
   <p>
    Syntax of the
    <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
     <code class="literal">
      JOIN_FIXED_ORDER
     </code>
    </a>
    hint:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa80196283"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">hint_name</em><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    Syntax of other join-order hints:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67021076"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">hint_name</em><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
<em class="replaceable">hint_name</em><span class="token punctuation">(</span><em class="replaceable">tbl_name</em><span class="token punctuation">[</span><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_name</em><span class="token punctuation">[</span><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    The syntax refers to these terms:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         hint_name
        </code>
       </em>
       : These hint names are
              permitted:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
           <code class="literal">
            JOIN_FIXED_ORDER
           </code>
          </a>
          :
                  Force the optimizer to join tables using the order in
                  which they appear in the
          <code class="literal">
           FROM
          </code>
          clause. This is the same as specifying
          <code class="literal">
           SELECT
                  STRAIGHT_JOIN
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
           <code class="literal">
            JOIN_ORDER
           </code>
          </a>
          : 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.
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
           <code class="literal">
            JOIN_PREFIX
           </code>
          </a>
          :
                  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.
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
           <code class="literal">
            JOIN_SUFFIX
           </code>
          </a>
          :
                  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.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       : The name of a table
              used in the statement. A hint that names tables applies to
              all tables that it names. The
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_FIXED_ORDER
        </code>
       </a>
       hint
              names no tables and applies to all tables in the
       <code class="literal">
        FROM
       </code>
       clause of the query block in which
              it occurs.
      </p>
      <p>
       If a table has an alias, hints must refer to the alias,
              not the table name.
      </p>
      <p>
       Table names in hints cannot be qualified with schema
              names.
      </p>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         query_block_name
        </code>
       </em>
       : The query
              block to which the hint applies. If the hint includes no
              leading
       <code class="literal">
        @
        <em class="replaceable">
         <code>
          query_block_name
         </code>
        </em>
       </code>
       ,
              the hint applies to the query block in which it occurs.
              For
       <code class="literal">
        <em class="replaceable">
         <code>
          tbl_name
         </code>
        </em>
        @
        <em class="replaceable">
         <code>
          query_block_name
         </code>
        </em>
       </code>
       syntax, the hint applies to the named table in the named
              query block. To assign a name to a query block, see
       <a class="xref" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
        Optimizer Hints for Naming Query Blocks
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    Example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22510367"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span>
<span class="token comment" spellcheck="true">/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
    JOIN_ORDER(t4@subq1, t3)
    JOIN_SUFFIX(t1) */</span>
<span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">JOIN</span> t2 <span class="token keyword">JOIN</span> t3
           <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>f1 <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(subq1) */</span> f1 <span class="token keyword">FROM</span> t4<span class="token punctuation">)</span>
             <span class="token operator">AND</span> t2<span class="token punctuation">.</span>f1 <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(subq2) */</span> f1 <span class="token keyword">FROM</span> t5<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Hints control the behavior of semijoin tables that are merged
          to the outer query block. If subqueries
    <code class="literal">
     subq1
    </code>
    and
    <code class="literal">
     subq2
    </code>
    are
          converted to semijoins, tables
    <code class="literal">
     t4@subq1
    </code>
    and
    <code class="literal">
     t5@subq2
    </code>
    are merged to the outer query
          block. In this case, the hint specified in the outer query
          block controls the behavior of
    <code class="literal">
     t4@subq1
    </code>
    ,
    <code class="literal">
     t5@subq2
    </code>
    tables.
   </p>
   <p>
    The optimizer resolves join-order hints according to these
          principles:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Multiple hint instances
      </p>
      <p>
       Only one
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_PREFIX
        </code>
       </a>
       and
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_SUFFIX
        </code>
       </a>
       hint of each
              type are applied. Any later hints of the same type are
              ignored with a warning.
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_ORDER
        </code>
       </a>
       can be
              specified several times.
      </p>
      <p>
       Examples:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa1714487"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2) */</span></code></pre>
      </div>
      <p>
       The second
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_PREFIX
        </code>
       </a>
       hint is ignored with a warning.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa11097719"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ JOIN_PREFIX(t1) JOIN_SUFFIX(t2) */</span></code></pre>
      </div>
      <p>
       Both hints are applicable. No warning occurs.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56475575"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ JOIN_ORDER(t1, t2) JOIN_ORDER(t2, t3) */</span></code></pre>
      </div>
      <p>
       Both hints are applicable. No warning occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       Conflicting hints
      </p>
      <p>
       In some cases hints can conflict, such as when
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_ORDER
        </code>
       </a>
       and
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_PREFIX
        </code>
       </a>
       have table
              orders that are impossible to apply at the same time:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa62329151"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">,</span> t2<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       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.
      </p>
     </li>
     <li class="listitem">
      <p>
       Ignored hints
      </p>
      <p>
       A hint is ignored if a table specified in the hint has a
              circular dependency.
      </p>
      <p>
       Example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa31596987"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */</span></code></pre>
      </div>
      <p>
       The
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_ORDER
        </code>
       </a>
       hint sets
              table
       <code class="literal">
        t2
       </code>
       dependent on
       <code class="literal">
        t1
       </code>
       . The
       <a class="link" href="optimizer-hints.html#optimizer-hints-join-order" title="Join-Order Optimizer Hints">
        <code class="literal">
         JOIN_PREFIX
        </code>
       </a>
       hint is
              ignored because table
       <code class="literal">
        t1
       </code>
       cannot be
              dependent on
       <code class="literal">
        t2
       </code>
       . Ignored hints are not
              displayed in extended
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       output.
      </p>
     </li>
     <li class="listitem">
      <p>
       Interaction with
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       tables
      </p>
      <p>
       The MySQL optimizer places
       <code class="literal">
        const
       </code>
       tables
              first in the join order, and the position of a
       <code class="literal">
        const
       </code>
       table cannot be affected by
              hints. References to
       <code class="literal">
        const
       </code>
       tables in
              join-order hints are ignored, although the hint is still
              applicable. For example, these are equivalent:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16459369"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">JOIN_ORDER<span class="token punctuation">(</span>t1<span class="token punctuation">,</span> <em class="replaceable">const_tbl</em><span class="token punctuation">,</span> t2<span class="token punctuation">)</span>
JOIN_ORDER<span class="token punctuation">(</span>t1<span class="token punctuation">,</span> t2<span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       Accepted hints shown in extended
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       output include
       <code class="literal">
        const
       </code>
       tables as they were specified.
      </p>
     </li>
     <li class="listitem">
      <p>
       Interaction with types of join operations
      </p>
      <p>
       MySQL supports several type of joins:
       <code class="literal">
        LEFT
       </code>
       ,
       <code class="literal">
        RIGHT
       </code>
       ,
       <code class="literal">
        INNER
       </code>
       ,
       <code class="literal">
        CROSS
       </code>
       ,
       <code class="literal">
        STRAIGHT_JOIN
       </code>
       . A hint that conflicts
              with the specified type of join is ignored with no
              warning.
      </p>
      <p>
       Example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa2295977"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ JOIN_PREFIX(t1, t2) */</span><span class="token keyword">FROM</span> t2 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t1<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Here a conflict occurs between the requested join order in
              the hint and the order required by the
       <code class="literal">
        LEFT
              JOIN
       </code>
       . The hint is ignored with no warning.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-table-level">
       </a>
       Table-Level Optimizer Hints
      </h4>
     </div>
    </div>
   </div>
   <p>
    Table-level hints affect:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Use of the Block Nested-Loop (BNL) and Batched Key Access
              (BKA) join-processing algorithms (see
       <a class="xref" href="bnl-bka-optimization.html" title="10.2.1.12 Block Nested-Loop and Batched Key Access Joins">
        Section 10.2.1.12, “Block Nested-Loop and Batched Key Access Joins”
       </a>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       Whether derived tables, view references, or common table
              expressions should be merged into the outer query block,
              or materialized using an internal temporary table.
      </p>
     </li>
     <li class="listitem">
      <p>
       Use of the derived table condition pushdown optimization.
              See
       <a class="xref" href="derived-condition-pushdown-optimization.html" title="10.2.2.5 Derived Condition Pushdown Optimization">
        Section 10.2.2.5, “Derived Condition Pushdown Optimization”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    These hint types apply to specific tables, or all tables in a
          query block.
   </p>
   <p>
    Syntax of table-level hints:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81427253"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">hint_name</em><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
<em class="replaceable">hint_name</em><span class="token punctuation">(</span><span class="token punctuation">[</span><em class="replaceable">tbl_name</em><span class="token variable">@<em class="replaceable">query_block_name</em></span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_name</em><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    The syntax refers to these terms:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         hint_name
        </code>
       </em>
       : These hint names are
              permitted:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            BKA
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            NO_BKA
           </code>
          </a>
          : Enable or
                  disable batched key access for the specified tables.
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            BNL
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            NO_BNL
           </code>
          </a>
          : Enable and
                  disable the hash join optimization.
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            DERIVED_CONDITION_PUSHDOWN
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            NO_DERIVED_CONDITION_PUSHDOWN
           </code>
          </a>
          :
                  Enable or disable use of derived table condition
                  pushdown for the specified tables. For more
                  information, see
          <a class="xref" href="derived-condition-pushdown-optimization.html" title="10.2.2.5 Derived Condition Pushdown Optimization">
           Section 10.2.2.5, “Derived Condition Pushdown Optimization”
          </a>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            HASH_JOIN
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            NO_HASH_JOIN
           </code>
          </a>
          : These
                  hints have no effect in MySQL 8.4; use
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            BNL
           </code>
          </a>
          or
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            NO_BNL
           </code>
          </a>
          instead.
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            MERGE
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
           <code class="literal">
            NO_MERGE
           </code>
          </a>
          : Enable
                  merging for the specified tables, view references or
                  common table expressions; or disable merging and use
                  materialization instead.
         </p>
         <a class="indexterm" name="idm46045223859952">
         </a>
         <a class="indexterm" name="idm46045223858464">
         </a>
         <a class="indexterm" name="idm46045223856976">
         </a>
        </li>
       </ul>
      </div>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        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.
       </p>
      </div>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       : 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.
      </p>
      <p>
       If a table has an alias, hints must refer to the alias,
              not the table name.
      </p>
      <p>
       Table names in hints cannot be qualified with schema
              names.
      </p>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         query_block_name
        </code>
       </em>
       : The query
              block to which the hint applies. If the hint includes no
              leading
       <code class="literal">
        @
        <em class="replaceable">
         <code>
          query_block_name
         </code>
        </em>
       </code>
       ,
              the hint applies to the query block in which it occurs.
              For
       <code class="literal">
        <em class="replaceable">
         <code>
          tbl_name
         </code>
        </em>
        @
        <em class="replaceable">
         <code>
          query_block_name
         </code>
        </em>
       </code>
       syntax, the hint applies to the named table in the named
              query block. To assign a name to a query block, see
       <a class="xref" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
        Optimizer Hints for Naming Query Blocks
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72585183"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_BKA(t1, t2) */</span> t1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t3<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_BNL() BKA(t1) */</span> t1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t3<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_MERGE(dt) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span> <span class="token keyword">AS</span> dt<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    A table-level hint applies to tables that receive records from
          previous tables, not sender tables. Consider this statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16737679"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ BNL(t2) */</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">,</span> t2<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If the optimizer chooses to process
    <code class="literal">
     t1
    </code>
    first, it applies a Block Nested-Loop join to
    <code class="literal">
     t2
    </code>
    by buffering the rows from
    <code class="literal">
     t1
    </code>
    before starting to read from
    <code class="literal">
     t2
    </code>
    . If the optimizer instead chooses to
          process
    <code class="literal">
     t2
    </code>
    first, the hint has no effect
          because
    <code class="literal">
     t2
    </code>
    is a sender table.
   </p>
   <p>
    For the
    <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
     <code class="literal">
      MERGE
     </code>
    </a>
    and
    <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
     <code class="literal">
      NO_MERGE
     </code>
    </a>
    hints, these
          precedence rules apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       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.)
      </p>
     </li>
     <li class="listitem">
      <p>
       A hint takes precedence over the
       <a class="link" href="switchable-optimizations.html#optflag_derived-merge">
        <code class="literal">
         derived_merge
        </code>
       </a>
       flag of
              the
       <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
        <code class="literal">
         optimizer_switch
        </code>
       </a>
       system variable.
      </p>
     </li>
     <li class="listitem">
      <p>
       For view references, an
       <code class="literal">
        ALGORITHM={MERGE|TEMPTABLE}
       </code>
       clause in
              the view definition takes precedence over a hint specified
              in the query referencing the view.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-index-level">
       </a>
       Index-Level Optimizer Hints
      </h4>
     </div>
    </div>
   </div>
   <p>
    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
    <a class="xref" href="select-optimization.html" title="10.2.1 Optimizing SELECT Statements">
     Section 10.2.1, “Optimizing SELECT Statements”
    </a>
    ).
   </p>
   <p>
    Syntax of index-level hints:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9244306"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">hint_name</em><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><em class="replaceable">index_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
<em class="replaceable">hint_name</em><span class="token punctuation">(</span><em class="replaceable">tbl_name</em><span class="token variable">@<em class="replaceable">query_block_name</em></span> <span class="token punctuation">[</span><em class="replaceable">index_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    The syntax refers to these terms:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         hint_name
        </code>
       </em>
       : These hint names are
              permitted:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223818832">
          </a>
          <a class="indexterm" name="idm46045223817760">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            GROUP_INDEX
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_GROUP_INDEX
           </code>
          </a>
          :
                  Enable or disable the specified index or indexes for
                  index scans for
          <code class="literal">
           GROUP BY
          </code>
          operations. Equivalent to the index hints
          <code class="literal">
           FORCE INDEX FOR GROUP BY
          </code>
          ,
          <code class="literal">
           IGNORE INDEX FOR GROUP BY
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223811040">
          </a>
          <a class="indexterm" name="idm46045223809968">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            INDEX
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_INDEX
           </code>
          </a>
          : Acts as
                  the combination of
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            JOIN_INDEX
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            GROUP_INDEX
           </code>
          </a>
          , and
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            ORDER_INDEX
           </code>
          </a>
          , forcing
                  the server to use the specified index or indexes for
                  any and all scopes, or as the combination of
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_JOIN_INDEX
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_GROUP_INDEX
           </code>
          </a>
          , and
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_ORDER_INDEX
           </code>
          </a>
          ,
                  which causes the server to ignore the specified index
                  or indexes for any and all scopes. Equivalent to
          <code class="literal">
           FORCE INDEX
          </code>
          ,
          <code class="literal">
           IGNORE
                  INDEX
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223796224">
          </a>
          <a class="indexterm" name="idm46045223795152">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            INDEX_MERGE
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_INDEX_MERGE
           </code>
          </a>
          :
                  Enable or disable the Index Merge access method for
                  the specified table or indexes. For information about
                  this access method, see
          <a class="xref" href="index-merge-optimization.html" title="10.2.1.3 Index Merge Optimization">
           Section 10.2.1.3, “Index Merge Optimization”
          </a>
          . These
                  hints apply to all three Index Merge algorithms.
         </p>
         <p>
          The
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            INDEX_MERGE
           </code>
          </a>
          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.
         </p>
         <p>
          The
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_INDEX_MERGE
           </code>
          </a>
          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.
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223785776">
          </a>
          <a class="indexterm" name="idm46045223784704">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            JOIN_INDEX
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_JOIN_INDEX
           </code>
          </a>
          :
                  Forces MySQL to use or ignore the specified index or
                  indexes for any access method, such as
          <code class="literal">
           ref
          </code>
          ,
          <code class="literal">
           range
          </code>
          ,
          <a class="link" href="switchable-optimizations.html#optflag_index-merge">
           <code class="literal">
            index_merge
           </code>
          </a>
          , and so
                  on. Equivalent to
          <code class="literal">
           FORCE INDEX FOR
                  JOIN
          </code>
          ,
          <code class="literal">
           IGNORE INDEX FOR
                  JOIN
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223776176">
          </a>
          <a class="indexterm" name="idm46045223775136">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            MRR
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_MRR
           </code>
          </a>
          : Enable or
                  disable MRR for the specified table or indexes. MRR
                  hints apply only to
          <code class="literal">
           InnoDB
          </code>
          and
          <code class="literal">
           MyISAM
          </code>
          tables. For information
                  about this access method, see
          <a class="xref" href="mrr-optimization.html" title="10.2.1.11 Multi-Range Read Optimization">
           Section 10.2.1.11, “Multi-Range Read Optimization”
          </a>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223768496">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_ICP
           </code>
          </a>
          : Disable ICP
                  for the specified table or indexes. By default, ICP is
                  a candidate optimization strategy, so there is no hint
                  for enabling it. For information about this access
                  method, see
          <a class="xref" href="index-condition-pushdown-optimization.html" title="10.2.1.6 Index Condition Pushdown Optimization">
           Section 10.2.1.6, “Index Condition Pushdown Optimization”
          </a>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223764384">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_RANGE_OPTIMIZATION
           </code>
          </a>
          :
                  Disable index range access for the specified table or
                  indexes. This hint also disables Index Merge and Loose
                  Index Scan for the table or indexes. By default, range
                  access is a candidate optimization strategy, so there
                  is no hint for enabling it.
         </p>
         <p>
          This hint may be useful when the number of ranges may
                  be high and range optimization would require many
                  resources.
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223760352">
          </a>
          <a class="indexterm" name="idm46045223759280">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            ORDER_INDEX
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_ORDER_INDEX
           </code>
          </a>
          :
                  Cause MySQL to use or to ignore the specified index or
                  indexes for sorting rows. Equivalent to
          <code class="literal">
           FORCE
                  INDEX FOR ORDER BY
          </code>
          ,
          <code class="literal">
           IGNORE INDEX
                  FOR ORDER BY
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <a class="indexterm" name="idm46045223753328">
          </a>
          <a class="indexterm" name="idm46045223752256">
          </a>
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            SKIP_SCAN
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_SKIP_SCAN
           </code>
          </a>
          : Enable
                  or disable the Skip Scan access method for the
                  specified table or indexes. For information about this
                  access method, see
          <a class="xref" href="range-optimization.html#range-access-skip-scan" title="Skip Scan Range Access Method">
           Skip Scan Range Access Method
          </a>
          .
         </p>
         <p>
          The
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            SKIP_SCAN
           </code>
          </a>
          hint
                  forces the optimizer to use Skip Scan for the
                  specified table using the specified set of indexes. If
                  no index is specified, the optimizer considers all
                  possible indexes and selects the least expensive one.
                  The hint may be ignored if the index is inapplicable
                  to the given statement.
         </p>
         <p>
          The
          <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
           <code class="literal">
            NO_SKIP_SCAN
           </code>
          </a>
          hint disables Skip Scan for the specified indexes. If
                  the hint specifies no indexes, Skip Scan is not
                  permitted for the table.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       : The table to which
              the hint applies.
      </p>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         index_name
        </code>
       </em>
       : 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.
      </p>
      <p>
       To refer to a primary key, use the name
       <code class="literal">
        PRIMARY
       </code>
       . To see the index names for a
              table, use
       <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
        <code class="literal">
         SHOW INDEX
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         query_block_name
        </code>
       </em>
       : The query
              block to which the hint applies. If the hint includes no
              leading
       <code class="literal">
        @
        <em class="replaceable">
         <code>
          query_block_name
         </code>
        </em>
       </code>
       ,
              the hint applies to the query block in which it occurs.
              For
       <code class="literal">
        <em class="replaceable">
         <code>
          tbl_name
         </code>
        </em>
        @
        <em class="replaceable">
         <code>
          query_block_name
         </code>
        </em>
       </code>
       syntax, the hint applies to the named table in the named
              query block. To assign a name to a query block, see
       <a class="xref" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
        Optimizer Hints for Naming Query Blocks
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48305550"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 f3, PRIMARY) */</span> f2 <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> f1 <span class="token operator">=</span> <span class="token string">'o'</span> <span class="token operator">AND</span> f2 <span class="token operator">=</span> f3 <span class="token operator">AND</span> f3 <span class="token operator">&lt;=</span> <span class="token number">4</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ MRR(t1) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> f2 <span class="token operator">&lt;=</span> <span class="token number">3</span> <span class="token operator">AND</span> <span class="token number">3</span> <span class="token operator">&lt;=</span> f3<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */</span> f1
  <span class="token keyword">FROM</span> t3 <span class="token keyword">WHERE</span> f1 <span class="token operator">&gt;</span> <span class="token number">30</span> <span class="token operator">AND</span> f1 <span class="token operator">&lt;</span> <span class="token number">33</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t3<span class="token punctuation">(</span>f1<span class="token punctuation">,</span> f2<span class="token punctuation">,</span> f3<span class="token punctuation">)</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_ICP(t2) */</span> t2<span class="token punctuation">.</span>f1<span class="token punctuation">,</span> t2<span class="token punctuation">.</span>f2<span class="token punctuation">,</span> t2<span class="token punctuation">.</span>f3 <span class="token keyword">FROM</span> t1<span class="token punctuation">,</span>t2
   <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>f1<span class="token operator">=</span>t2<span class="token punctuation">.</span>f1 <span class="token operator">AND</span> t2<span class="token punctuation">.</span>f2 <span class="token operator">BETWEEN</span> t1<span class="token punctuation">.</span>f1
   <span class="token operator">AND</span> t1<span class="token punctuation">.</span>f2 <span class="token operator">AND</span> t2<span class="token punctuation">.</span>f2 <span class="token operator">+</span> <span class="token number">1</span> <span class="token operator">&gt;=</span> t1<span class="token punctuation">.</span>f1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SKIP_SCAN(t1 PRIMARY) */</span> f1<span class="token punctuation">,</span> f2
  <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> f2 <span class="token operator">&gt;</span> <span class="token number">40</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    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 the
    <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
     <code class="literal">
      optimizer_switch
     </code>
    </a>
    system
          variable or index hints.
   </p>
   <p>
    Assume that table
    <code class="literal">
     t1
    </code>
    has columns
    <code class="literal">
     a
    </code>
    ,
    <code class="literal">
     b
    </code>
    ,
    <code class="literal">
     c
    </code>
    , and
    <code class="literal">
     d
    </code>
    ; and that
          indexes named
    <code class="literal">
     i_a
    </code>
    ,
    <code class="literal">
     i_b
    </code>
    ,
          and
    <code class="literal">
     i_c
    </code>
    exist on
    <code class="literal">
     a
    </code>
    ,
    <code class="literal">
     b
    </code>
    , and
    <code class="literal">
     c
    </code>
    , respectively:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99732889"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> a <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> b <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> c <span class="token operator">=</span> <span class="token number">3</span> <span class="token operator">AND</span> d <span class="token operator">=</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Index Merge is used for
    <code class="literal">
     (i_a, i_b, i_c)
    </code>
    in
          this case.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7580419"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> c <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> d <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Index Merge is used for
    <code class="literal">
     (i_b, i_c)
    </code>
    in this
          case.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa52627283"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 i_a, i_b) NO_INDEX_MERGE(t1 i_b) */</span></code></pre>
   </div>
   <p>
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      NO_INDEX_MERGE
     </code>
    </a>
    is ignored
          because there is a preceding hint for the same table.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa97399002"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ NO_INDEX_MERGE(t1 i_a, i_b) INDEX_MERGE(t1 i_b) */</span></code></pre>
   </div>
   <p>
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      INDEX_MERGE
     </code>
    </a>
    is ignored
          because there is a preceding hint for the same table.
   </p>
   <p>
    For the
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      INDEX_MERGE
     </code>
    </a>
    and
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      NO_INDEX_MERGE
     </code>
    </a>
    optimizer
          hints, these precedence rules apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If an optimizer hint is specified and is applicable, it
              takes precedence over the Index Merge-related flags of the
       <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
        <code class="literal">
         optimizer_switch
        </code>
       </a>
       system
              variable.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64115511"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> optimizer_switch<span class="token operator">=</span><span class="token string">'index_merge_intersection=off'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 i_b, i_c) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
<span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> c <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> d <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The hint takes precedence over
       <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
        <code class="literal">
         optimizer_switch
        </code>
       </a>
       . Index
              Merge is used for
       <code class="literal">
        (i_b, i_c)
       </code>
       in this
              case.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9673671"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> optimizer_switch<span class="token operator">=</span><span class="token string">'index_merge_intersection=on'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 i_b) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
<span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> c <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> d <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The hint specifies only one index, so it is inapplicable,
              and the
       <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
        <code class="literal">
         optimizer_switch
        </code>
       </a>
       flag (
       <code class="literal">
        on
       </code>
       ) applies. Index Merge is used
              if the optimizer assesses it to be cost efficient.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11860701"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> optimizer_switch<span class="token operator">=</span><span class="token string">'index_merge_intersection=off'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 i_b) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
<span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">AND</span> c <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> d <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The hint specifies only one index, so it is inapplicable,
              and the
       <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
        <code class="literal">
         optimizer_switch
        </code>
       </a>
       flag (
       <code class="literal">
        off
       </code>
       ) applies. Index Merge is not
              used.
      </p>
     </li>
     <li class="listitem">
      <p>
       The index-level optimizer hints
       <code class="literal">
        GROUP_INDEX
       </code>
       ,
       <code class="literal">
        INDEX
       </code>
       ,
       <code class="literal">
        JOIN_INDEX
       </code>
       , and
       <code class="literal">
        ORDER_INDEX
       </code>
       all take precedence over
              the equivalent
       <code class="literal">
        FORCE INDEX
       </code>
       hints; that
              is, they cause the
       <code class="literal">
        FORCE INDEX
       </code>
       hints to
              be ignored. Likewise, the
       <code class="literal">
        NO_GROUP_INDEX
       </code>
       ,
       <code class="literal">
        NO_INDEX
       </code>
       ,
       <code class="literal">
        NO_JOIN_INDEX
       </code>
       , and
       <code class="literal">
        NO_ORDER_INDEX
       </code>
       hints all take
              precedence over any
       <code class="literal">
        IGNORE INDEX
       </code>
       equivalents, also causing them to be ignored.
      </p>
      <p>
       The index-level optimizer hints
       <code class="literal">
        GROUP_INDEX
       </code>
       ,
       <code class="literal">
        NO_GROUP_INDEX
       </code>
       ,
       <code class="literal">
        INDEX
       </code>
       ,
       <code class="literal">
        NO_INDEX
       </code>
       ,
       <code class="literal">
        JOIN_INDEX
       </code>
       ,
       <code class="literal">
        NO_JOIN_INDEX
       </code>
       ,
       <code class="literal">
        ORDER_INDEX
       </code>
       , and
       <code class="literal">
        NO_ORDER_INDEX
       </code>
       hints all take
              precedence over all other optimizer hints, including other
              index-level optimizer hints. Any other optimizer hints are
              applied only to the indexes permitted by these.
      </p>
      <p>
       The
       <code class="literal">
        GROUP_INDEX
       </code>
       ,
       <code class="literal">
        INDEX
       </code>
       ,
       <code class="literal">
        JOIN_INDEX
       </code>
       ,
              and
       <code class="literal">
        ORDER_INDEX
       </code>
       hints are all
              equivalent to
       <code class="literal">
        FORCE INDEX
       </code>
       and not to
       <code class="literal">
        USE INDEX
       </code>
       . This is because using one or
              more of these hints means that a table scan is used only
              if there is no way to use one of the named indexes to find
              rows in the table. To cause MySQL to use the same index or
              set of indexes as with a given instance of
       <code class="literal">
        USE
              INDEX
       </code>
       , you can use
       <code class="literal">
        NO_INDEX
       </code>
       ,
       <code class="literal">
        NO_JOIN_INDEX
       </code>
       ,
       <code class="literal">
        NO_GROUP_INDEX
       </code>
       ,
       <code class="literal">
        NO_ORDER_INDEX
       </code>
       , or some combination of
              these.
      </p>
      <p>
       To replicate the effect that
       <code class="literal">
        USE INDEX
       </code>
       has in the query
       <code class="literal">
        SELECT a,c FROM t1 USE INDEX FOR
              ORDER BY (i_a) ORDER BY a
       </code>
       , you can use the
       <code class="literal">
        NO_ORDER_INDEX
       </code>
       optimizer hint to cover
              all indexes on the table except the one that is desired
              like this:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33888857"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_ORDER_INDEX(t1 i_b,i_c) */</span> a<span class="token punctuation">,</span>c
    <span class="token keyword">FROM</span> t1
    <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Attempting to combine
       <code class="literal">
        NO_ORDER_INDEX
       </code>
       for the table as a whole with
       <code class="literal">
        USE INDEX FOR ORDER
              BY
       </code>
       does not work to do this, because
       <code class="literal">
        NO_ORDER_BY
       </code>
       causes
       <code class="literal">
        USE
              INDEX
       </code>
       to be ignored, as shown here:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13949209"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_ORDER_INDEX(t1) */</span> a<span class="token punctuation">,</span>c <span class="token keyword">FROM</span> t1
<span class="token prompt">    -&gt;</span>     <span class="token keyword">USE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>i_a<span class="token punctuation">)</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t1
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> ALL
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> NULL
      key_len<span class="token punctuation">:</span> NULL
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 256
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using filesort</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        USE INDEX
       </code>
       ,
       <code class="literal">
        FORCE
              INDEX
       </code>
       , and
       <code class="literal">
        IGNORE INDEX
       </code>
       index
              hints have higher priority than the
       <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
        <code class="literal">
         INDEX_MERGE
        </code>
       </a>
       and
       <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
        <code class="literal">
         NO_INDEX_MERGE
        </code>
       </a>
       optimizer
              hints.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20681468"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 i_a, i_b, i_c) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> i_a</code></pre>
      </div>
      <p>
       <code class="literal">
        IGNORE INDEX
       </code>
       takes precedence over
       <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
        <code class="literal">
         INDEX_MERGE
        </code>
       </a>
       , so index
       <code class="literal">
        i_a
       </code>
       is excluded from the possible
              ranges for Index Merge.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa84769950"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">/*+ NO_INDEX_MERGE(t1 i_a, i_b) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FORCE</span> <span class="token keyword">INDEX</span> i_a<span class="token punctuation">,</span> i_b</code></pre>
      </div>
      <p>
       Index Merge is disallowed for
       <code class="literal">
        i_a, i_b
       </code>
       because of
       <code class="literal">
        FORCE INDEX
       </code>
       , but the
              optimizer is forced to use either
       <code class="literal">
        i_a
       </code>
       or
       <code class="literal">
        i_b
       </code>
       for
       <a class="link" href="explain-output.html#jointype_range">
        <code class="literal">
         range
        </code>
       </a>
       or
       <a class="link" href="explain-output.html#jointype_ref">
        <code class="literal">
         ref
        </code>
       </a>
       access. There are
              no conflicts; both hints are applicable.
      </p>
     </li>
     <li class="listitem">
      <p>
       If an
       <code class="literal">
        IGNORE INDEX
       </code>
       hint names multiple
              indexes, those indexes are unavailable for Index Merge.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        FORCE INDEX
       </code>
       and
       <code class="literal">
        USE
              INDEX
       </code>
       hints make only the named indexes to be
              available for Index Merge.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26171502"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ INDEX_MERGE(t1 i_a, i_b, i_c) */</span> a <span class="token keyword">FROM</span> t1
<span class="token keyword">FORCE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i_a<span class="token punctuation">,</span> i_b<span class="token punctuation">)</span> <span class="token keyword">WHERE</span> c <span class="token operator">=</span> <span class="token string">'h'</span> <span class="token operator">AND</span> a <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> b <span class="token operator">=</span> <span class="token string">'b'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The Index Merge intersection access algorithm is used for
       <code class="literal">
        (i_a, i_b)
       </code>
       . The same is true if
       <code class="literal">
        FORCE INDEX
       </code>
       is changed to
       <code class="literal">
        USE
              INDEX
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-subquery">
       </a>
       Subquery Optimizer Hints
      </h4>
     </div>
    </div>
   </div>
   <p>
    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 or
    <code class="literal">
     IN
    </code>
    -to-
    <code class="literal">
     EXISTS
    </code>
    transformations. For more information about these
          optimizations, see
    <a class="xref" href="subquery-optimization.html" title="10.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions">
     Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table
        Expressions”
    </a>
    .
   </p>
   <p>
    Syntax of hints that affect semijoin strategies:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa98800033"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">hint_name</em><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">strategy</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">strategy</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    The syntax refers to these terms:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         hint_name
        </code>
       </em>
       : These hint names are
              permitted:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
           <code class="literal">
            SEMIJOIN
           </code>
          </a>
          ,
          <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
           <code class="literal">
            NO_SEMIJOIN
           </code>
          </a>
          : Enable
                  or disable the named semijoin strategies.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         strategy
        </code>
       </em>
       : A semijoin strategy
              to be enabled or disabled. These strategy names are
              permitted:
       <code class="literal">
        DUPSWEEDOUT
       </code>
       ,
       <code class="literal">
        FIRSTMATCH
       </code>
       ,
       <code class="literal">
        LOOSESCAN
       </code>
       ,
       <code class="literal">
        MATERIALIZATION
       </code>
       .
      </p>
      <p>
       For
       <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
        <code class="literal">
         SEMIJOIN
        </code>
       </a>
       hints, if
              no strategies are named, semijoin is used if possible
              based on the strategies enabled according to the
       <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
        <code class="literal">
         optimizer_switch
        </code>
       </a>
       system
              variable. If strategies are named but inapplicable for the
              statement,
       <code class="literal">
        DUPSWEEDOUT
       </code>
       is used.
      </p>
      <p>
       For
       <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
        <code class="literal">
         NO_SEMIJOIN
        </code>
       </a>
       hints,
              if no strategies are named, semijoin is not used. If
              strategies are named that rule out all applicable
              strategies for the statement,
       <code class="literal">
        DUPSWEEDOUT
       </code>
       is used.
      </p>
     </li>
    </ul>
   </div>
   <p>
    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.
    <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
     <code class="literal">
      SEMIJOIN
     </code>
    </a>
    and
    <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
     <code class="literal">
      NO_SEMIJOIN
     </code>
    </a>
    hints can still
          be used to enable or disable semijoin transformations for such
          nested subqueries.
   </p>
   <p>
    If
    <code class="literal">
     DUPSWEEDOUT
    </code>
    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
    <a class="link" href="server-system-variables.html#sysvar_optimizer_prune_level">
     <code class="literal">
      optimizer_prune_level=0
     </code>
    </a>
    .
   </p>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96647553"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2
  <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(subq1) */</span> a <span class="token keyword">FROM</span> t3<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2
  <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(subq1) */</span> a <span class="token keyword">FROM</span> t3<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Syntax of hints that affect whether to use subquery
          materialization or
    <code class="literal">
     IN
    </code>
    -to-
    <code class="literal">
     EXISTS
    </code>
    transformations:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa43648737"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">SUBQUERY<span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">@<em class="replaceable">query_block_name</em></span><span class="token punctuation">]</span> <em class="replaceable">strategy</em><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    The hint name is always
    <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
     <code class="literal">
      SUBQUERY
     </code>
    </a>
    .
   </p>
   <p>
    For
    <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
     <code class="literal">
      SUBQUERY
     </code>
    </a>
    hints, these
    <em class="replaceable">
     <code>
      strategy
     </code>
    </em>
    values are permitted:
    <code class="literal">
     INTOEXISTS
    </code>
    ,
    <code class="literal">
     MATERIALIZATION
    </code>
    .
   </p>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32323990"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SUBQUERY(MATERIALIZATION) */</span> a <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SUBQUERY(INTOEXISTS) */</span> a <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For semijoin and
    <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
     <code class="literal">
      SUBQUERY
     </code>
    </a>
    hints, a leading
    <code class="literal">
     @
     <em class="replaceable">
      <code>
       query_block_name
      </code>
     </em>
    </code>
    specifies the query block to which the hint applies. If the
          hint includes no leading
    <code class="literal">
     @
     <em class="replaceable">
      <code>
       query_block_name
      </code>
     </em>
    </code>
    ,
          the hint applies to the query block in which it occurs. To
          assign a name to a query block, see
    <a class="xref" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
     Optimizer Hints for Naming Query Blocks
    </a>
    .
   </p>
   <p>
    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.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-execution-time">
       </a>
       Statement Execution Time Optimizer Hints
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="optimizer-hints.html#optimizer-hints-execution-time" title="Statement Execution Time Optimizer Hints">
     <code class="literal">
      MAX_EXECUTION_TIME
     </code>
    </a>
    hint
          is permitted only for
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statements. It places a limit
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    (a
          timeout value in milliseconds) on how long a statement is
          permitted to execute before the server terminates it:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa5294290"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">MAX_EXECUTION_TIME<span class="token punctuation">(</span><em class="replaceable">N</em><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    Example with a timeout of 1 second (1000 milliseconds):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20901840"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ MAX_EXECUTION_TIME(1000) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    The
    <a class="link" href="optimizer-hints.html#optimizer-hints-execution-time" title="Statement Execution Time Optimizer Hints">
     <code class="literal">
      MAX_EXECUTION_TIME(
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      )
     </code>
    </a>
    hint sets a statement execution timeout of
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    milliseconds. If this option is
          absent or
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    is 0, the statement
          timeout established by the
    <a class="link" href="server-system-variables.html#sysvar_max_execution_time">
     <code class="literal">
      max_execution_time
     </code>
    </a>
    system
          variable applies.
   </p>
   <p>
    The
    <a class="link" href="optimizer-hints.html#optimizer-hints-execution-time" title="Statement Execution Time Optimizer Hints">
     <code class="literal">
      MAX_EXECUTION_TIME
     </code>
    </a>
    hint
          is applicable as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For statements with multiple
       <code class="literal">
        SELECT
       </code>
       keywords, such as unions or statements with subqueries,
       <a class="link" href="optimizer-hints.html#optimizer-hints-execution-time" title="Statement Execution Time Optimizer Hints">
        <code class="literal">
         MAX_EXECUTION_TIME
        </code>
       </a>
       applies to the entire statement and must appear after the
              first
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       It applies to read-only
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statements.
              Statements that are not read only are those that invoke a
              stored function that modifies data as a side effect.
      </p>
     </li>
     <li class="listitem">
      <p>
       It does not apply to
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statements in stored programs and is ignored.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-set-var">
       </a>
       Variable-Setting Hint Syntax
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045223567536">
   </a>
   <a class="indexterm" name="idm46045223566496">
   </a>
   <a class="indexterm" name="idm46045223565008">
   </a>
   <a class="indexterm" name="idm46045223563520">
   </a>
   <p>
    The
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    hint sets the
          session value of a system variable temporarily (for the
          duration of a single statement). Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59533008"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(sort_buffer_size = 16M) */</span> <span class="token keyword">name</span> <span class="token keyword">FROM</span> people <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">name</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(foreign_key_checks=OFF) */</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Syntax of the
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    hint:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa31251002"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">SET_VAR<span class="token punctuation">(</span><em class="replaceable">var_name</em> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    <em class="replaceable">
     <code>
      var_name
     </code>
    </em>
    names a system variable
          that has a session value (although not all such variables can
          be named, as explained later).
    <em class="replaceable">
     <code>
      value
     </code>
    </em>
    is the value to assign to the
          variable; the value must be a scalar.
   </p>
   <p>
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    makes a temporary
          variable change, as demonstrated by these statements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79866756"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@unique_checks</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @@unique_checks <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>               1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(unique_checks=OFF) */</span> <span class="token variable">@@unique_checks</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @@unique_checks <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>               0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@unique_checks</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @@unique_checks <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>               1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    With
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    , 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:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56896115"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@saved_val</span> <span class="token operator">=</span> <span class="token variable">@@SESSION.<em class="replaceable">var_name</em></span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.<em class="replaceable">var_name</em></span> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.<em class="replaceable">var_name</em></span> <span class="token operator">=</span> <span class="token variable">@saved_val</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The sequence can be replaced by this single statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa33920158"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">/</span><span class="token operator">*</span><span class="token operator">+</span> SET_VAR<span class="token punctuation">(</span><em class="replaceable">var_name</em> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    Standalone
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    statements permit any of these syntaxes for naming session
          variables:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5628596"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token keyword">SESSION</span> <em class="replaceable">var_name</em> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.<em class="replaceable">var_name</em></span> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@.<em class="replaceable">var_name</em></span> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Because the
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    hint
          applies only to session variables, session scope is implicit,
          and
    <code class="literal">
     SESSION
    </code>
    ,
    <code class="literal">
     @@SESSION.
    </code>
    ,
          and
    <code class="literal">
     @@
    </code>
    are neither needed nor permitted.
          Including explicit session-indicator syntax results in the
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    hint being ignored
          with a warning.
   </p>
   <p>
    Not all session variables are permitted for use with
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    . Individual system
          variable descriptions indicate whether each variable is
          hintable; see
    <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
     Section 7.1.8, “Server System Variables”
    </a>
    . You
          can also check a system variable at runtime by attempting to
          use it with
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    . If the
          variable is not hintable, a warning occurs:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95545002"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(collation_server = 'utf8mb4') */</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set, 1 warning (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  Level<span class="token punctuation">:</span> Warning
   Code<span class="token punctuation">:</span> 4537
Message<span class="token punctuation">:</span> Variable 'collation_server' cannot be set using SET_VAR hint.</span></code></pre>
   </div>
   <p>
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    syntax permits
          setting only a single variable, but multiple hints can be
          given to set multiple variables:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45650358"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off')
           SET_VAR(max_heap_table_size = 1G) */</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    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:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46428061"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(max_heap_table_size = 1G)
           SET_VAR(max_heap_table_size = 3G) */</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    In this case, the second hint is ignored with a warning that
          it is conflicting.
   </p>
   <p>
    A
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    hint is ignored
          with a warning if no system variable has the specified name or
          the variable value is incorrect:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95397663"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(max_size = 1G) */</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For the first statement, there is no
    <code class="literal">
     max_size
    </code>
    variable. For the second
          statement,
    <a class="link" href="switchable-optimizations.html#optflag_mrr-cost-based">
     <code class="literal">
      mrr_cost_based
     </code>
    </a>
    takes values of
    <code class="literal">
     on
    </code>
    or
    <code class="literal">
     off
    </code>
    , so attempting to set it to
    <code class="literal">
     yes
    </code>
    is incorrect. In each case, the hint is
          ignored with a warning.
   </p>
   <p>
    The
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    hint is
          permitted only at the statement level. If used in a subquery,
          the hint is ignored with a warning.
   </p>
   <p>
    Replicas ignore
    <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
     <code class="literal">
      SET_VAR
     </code>
    </a>
    hints in replicated statements to avoid the potential for
          security issues.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-resource-group">
       </a>
       Resource Group Hint Syntax
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="optimizer-hints.html#optimizer-hints-resource-group" title="Resource Group Hint Syntax">
     <code class="literal">
      RESOURCE_GROUP
     </code>
    </a>
    optimizer
          hint is used for resource group management (see
    <a class="xref" href="resource-groups.html" title="7.1.16 Resource Groups">
     Section 7.1.16, “Resource Groups”
    </a>
    ). This hint assigns the
          thread that executes a statement to the named resource group
          temporarily (for the duration of the statement). It requires
          the
    <a class="link" href="privileges-provided.html#priv_resource-group-admin">
     <code class="literal">
      RESOURCE_GROUP_ADMIN
     </code>
    </a>
    or
    <a class="link" href="privileges-provided.html#priv_resource-group-user">
     <code class="literal">
      RESOURCE_GROUP_USER
     </code>
    </a>
    privilege.
   </p>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31416534"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ RESOURCE_GROUP(USR_default) */</span> <span class="token keyword">name</span> <span class="token keyword">FROM</span> people <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">name</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token comment" spellcheck="true">/*+ RESOURCE_GROUP(Batch) */</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Syntax of the
    <a class="link" href="optimizer-hints.html#optimizer-hints-resource-group" title="Resource Group Hint Syntax">
     <code class="literal">
      RESOURCE_GROUP
     </code>
    </a>
    hint:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa83148283"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">RESOURCE_GROUP<span class="token punctuation">(</span><em class="replaceable">group_name</em><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    <em class="replaceable">
     <code>
      group_name
     </code>
    </em>
    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.
   </p>
   <p>
    The
    <a class="link" href="optimizer-hints.html#optimizer-hints-resource-group" title="Resource Group Hint Syntax">
     <code class="literal">
      RESOURCE_GROUP
     </code>
    </a>
    hint must
          appear after the initial statement keyword
          (
    <code class="literal">
     SELECT
    </code>
    ,
    <code class="literal">
     INSERT
    </code>
    ,
    <code class="literal">
     REPLACE
    </code>
    ,
    <code class="literal">
     UPDATE
    </code>
    , or
    <code class="literal">
     DELETE
    </code>
    ).
   </p>
   <p>
    An alternative to
    <a class="link" href="optimizer-hints.html#optimizer-hints-resource-group" title="Resource Group Hint Syntax">
     <code class="literal">
      RESOURCE_GROUP
     </code>
    </a>
    is the
    <a class="link" href="set-resource-group.html" title="15.7.2.4 SET RESOURCE GROUP Statement">
     <code class="literal">
      SET RESOURCE GROUP
     </code>
    </a>
    statement,
          which nontemporarily assigns threads to a resource group. See
    <a class="xref" href="set-resource-group.html" title="15.7.2.4 SET RESOURCE GROUP Statement">
     Section 15.7.2.4, “SET RESOURCE GROUP Statement”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="optimizer-hints-query-block-naming">
       </a>
       Optimizer Hints for Naming Query Blocks
      </h4>
     </div>
    </div>
   </div>
   <p>
    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
    <a class="link" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
     <code class="literal">
      QB_NAME
     </code>
    </a>
    hint, which assigns
          a name to the query block in which it occurs:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa94013876"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">QB_NAME<span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">name</em></span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    <a class="link" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
     <code class="literal">
      QB_NAME
     </code>
    </a>
    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:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78327739"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    <a class="link" href="optimizer-hints.html#optimizer-hints-query-block-naming" title="Optimizer Hints for Naming Query Blocks">
     <code class="literal">
      QB_NAME
     </code>
    </a>
    hints assign names
          to query blocks in the statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59528538"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(qb1) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(qb2) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(qb3) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    Then other hints can use those names to refer to the
          appropriate query blocks:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa91041312"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(qb2) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(qb3) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    The resulting effect is as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
        <code class="literal">
         MRR(@qb1 t1)
        </code>
       </a>
       applies to
              table
       <code class="literal">
        t1
       </code>
       in query block
       <code class="literal">
        qb1
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
        <code class="literal">
         BKA(@qb2)
        </code>
       </a>
       applies to
              query block
       <code class="literal">
        qb2
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
        <code class="literal">
         NO_MRR(@qb3 t1 idx1,
              id2)
        </code>
       </a>
       applies to indexes
       <code class="literal">
        idx1
       </code>
       and
       <code class="literal">
        idx2
       </code>
       in table
       <code class="literal">
        t1
       </code>
       in query block
       <code class="literal">
        qb3
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    Query block names are identifiers and follow the usual rules
          about what names are valid and how to quote them (see
    <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
     Section 11.2, “Schema Object Names”
    </a>
    ). For example, a query block
          name that contains spaces must be quoted, which can be done
          using backticks:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa91611930"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ BKA(@`my hint name`) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME(`my hint name`) */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    If the
    <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
     <code class="literal">
      ANSI_QUOTES
     </code>
    </a>
    SQL mode
          is enabled, it is also possible to quote query block names
          within double quotation marks:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76318250"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ BKA(@"my hint name") */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ QB_NAME("my hint name") */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
