<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="condition-filtering">
      </a>
      10.2.1.13 Condition Filtering
     </h4>
    </div>
   </div>
  </div>
  <p>
   In join processing, prefix rows are those rows passed from one
          table in a join to the next. In general, the optimizer
          attempts to put tables with low prefix counts early in the
          join order to keep the number of row combinations from
          increasing rapidly. To the extent that the optimizer can use
          information about conditions on rows selected from one table
          and passed to the next, the more accurately it can compute row
          estimates and choose the best execution plan.
  </p>
  <p>
   Without condition filtering, the prefix row count for a table
          is based on the estimated number of rows selected by the
   <code class="literal">
    WHERE
   </code>
   clause according to whichever access
          method the optimizer chooses. Condition filtering enables the
          optimizer to use other relevant conditions in the
   <code class="literal">
    WHERE
   </code>
   clause not taken into account by the
          access method, and thus improve its prefix row count
          estimates. For example, even though there might be an
          index-based access method that can be used to select rows from
          the current table in a join, there might also be additional
          conditions for the table in the
   <code class="literal">
    WHERE
   </code>
   clause that can filter (further restrict) the estimate for
          qualifying rows passed to the next table.
  </p>
  <p>
   A condition contributes to the filtering estimate only if:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      It refers to the current table.
     </p>
    </li>
    <li class="listitem">
     <p>
      It depends on a constant value or values from earlier
              tables in the join sequence.
     </p>
    </li>
    <li class="listitem">
     <p>
      It was not already taken into account by the access
              method.
     </p>
    </li>
   </ul>
  </div>
  <p>
   In
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output, the
   <code class="literal">
    rows
   </code>
   column indicates the row estimate for
          the chosen access method, and the
   <code class="literal">
    filtered
   </code>
   column reflects the effect of condition filtering.
   <code class="literal">
    filtered
   </code>
   values are expressed as
          percentages. The maximum value is 100, which means no
          filtering of rows occurred. Values decreasing from 100
          indicate increasing amounts of filtering.
  </p>
  <p>
   The prefix row count (the number of rows estimated to be
          passed from the current table in a join to the next) is the
          product of the
   <code class="literal">
    rows
   </code>
   and
   <code class="literal">
    filtered
   </code>
   values. That is, the prefix row
          count is the estimated row count, reduced by the estimated
          filtering effect. For example, if
   <code class="literal">
    rows
   </code>
   is
          1000 and
   <code class="literal">
    filtered
   </code>
   is 20%, condition
          filtering reduces the estimated row count of 1000 to a prefix
          row count of 1000 × 20% = 1000 × .2 = 200.
  </p>
  <p>
   Consider the following query:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95816594"><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 keyword">FROM</span> employee <span class="token keyword">JOIN</span> department <span class="token keyword">ON</span> employee<span class="token punctuation">.</span>dept_no <span class="token operator">=</span> department<span class="token punctuation">.</span>dept_no
  <span class="token keyword">WHERE</span> employee<span class="token punctuation">.</span>first_name <span class="token operator">=</span> <span class="token string">'John'</span>
  <span class="token operator">AND</span> employee<span class="token punctuation">.</span>hire_date <span class="token operator">BETWEEN</span> <span class="token string">'2018-01-01'</span> <span class="token operator">AND</span> <span class="token string">'2018-06-01'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose that the data set has these characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <code class="literal">
       employee
      </code>
      table has 1024 rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       department
      </code>
      table has 12 rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      Both tables have an index on
      <code class="literal">
       dept_no
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       employee
      </code>
      table has an index on
      <code class="literal">
       first_name
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      8 rows satisfy this condition on
      <code class="literal">
       employee.first_name
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa75874779"><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">employee<span class="token punctuation">.</span>first_name <span class="token operator">=</span> <span class="token string">'John'</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      150 rows satisfy this condition on
      <code class="literal">
       employee.hire_date
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa54153249"><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">employee<span class="token punctuation">.</span>hire_date <span class="token operator">BETWEEN</span> <span class="token string">'2018-01-01'</span> <span class="token operator">AND</span> <span class="token string">'2018-06-01'</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      1 row satisfies both conditions:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8630614"><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">employee<span class="token punctuation">.</span>first_name <span class="token operator">=</span> <span class="token string">'John'</span>
<span class="token operator">AND</span> employee<span class="token punctuation">.</span>hire_date <span class="token operator">BETWEEN</span> <span class="token string">'2018-01-01'</span> <span class="token operator">AND</span> <span class="token string">'2018-06-01'</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Without condition filtering,
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   produces output like
          this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa97289758"><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-none">+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 100.00   |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+</code></pre>
  </div>
  <p>
   For
   <code class="literal">
    employee
   </code>
   , the access method on the
   <code class="literal">
    name
   </code>
   index picks up the 8 rows that match a
          name of
   <code class="literal">
    'John'
   </code>
   . No filtering is done
          (
   <code class="literal">
    filtered
   </code>
   is 100%), so all rows are prefix
          rows for the next table: The prefix row count is
   <code class="literal">
    rows
   </code>
   ×
   <code class="literal">
    filtered
   </code>
   =
          8 × 100% = 8.
  </p>
  <p>
   With condition filtering, the optimizer additionally takes
          into account conditions from the
   <code class="literal">
    WHERE
   </code>
   clause not taken into account by the access method. In this
          case, the optimizer uses heuristics to estimate a filtering
          effect of 16.31% for the
   <a class="link" href="comparison-operators.html#operator_between">
    <code class="literal">
     BETWEEN
    </code>
   </a>
   condition on
   <code class="literal">
    employee.hire_date
   </code>
   . As a
          result,
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   produces output
          like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa75839927"><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-none">+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 16.31    |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+</code></pre>
  </div>
  <p>
   Now the prefix row count is
   <code class="literal">
    rows
   </code>
   ×
   <code class="literal">
    filtered
   </code>
   = 8 × 16.31% = 1.3, which
          more closely reflects actual data set.
  </p>
  <p>
   Normally, the optimizer does not calculate the condition
          filtering effect (prefix row count reduction) for the last
          joined table because there is no next table to pass rows to.
          An exception occurs for
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   : To provide more
          information, the filtering effect is calculated for all joined
          tables, including the last one.
  </p>
  <p>
   To control whether the optimizer considers additional
          filtering conditions, use the
   <a class="link" href="switchable-optimizations.html#optflag_condition-fanout-filter">
    <code class="literal">
     condition_fanout_filter
    </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 (see
   <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
    Section 10.9.2, “Switchable Optimizations”
   </a>
   ). This flag is
          enabled by default but can be disabled to suppress condition
          filtering (for example, if a particular query is found to
          yield better performance without it).
  </p>
  <p>
   If the optimizer overestimates the effect of condition
          filtering, performance may be worse than if condition
          filtering is not used. In such cases, these techniques may
          help:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If a column is not indexed, index it so that the optimizer
              has some information about the distribution of column
              values and can improve its row estimates.
     </p>
    </li>
    <li class="listitem">
     <p>
      Similarly, if no column histogram information is
              available, generate a histogram (see
      <a class="xref" href="optimizer-statistics.html" title="10.9.6 Optimizer Statistics">
       Section 10.9.6, “Optimizer Statistics”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      Change the join order. Ways to accomplish this include
              join-order optimizer hints (see
      <a class="xref" href="optimizer-hints.html" title="10.9.3 Optimizer Hints">
       Section 10.9.3, “Optimizer Hints”
      </a>
      ),
      <code class="literal">
       STRAIGHT_JOIN
      </code>
      immediately following the
      <code class="literal">
       SELECT
      </code>
      , and the
      <code class="literal">
       STRAIGHT_JOIN
      </code>
      join operator.
     </p>
    </li>
    <li class="listitem">
     <p>
      Disable condition filtering for the session:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16379787"><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">'condition_fanout_filter=off'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Or, for a given query, using an optimizer hint:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa86543194"><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 = 'condition_fanout_filter=off') */</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
