Optimizing SELECT Statements To use a BNL or BKA hint to enable join buffering for any inner table of an outer join, join buffering must be enabled for all inner tables of the outer join. 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. Without condition filtering, the prefix row count for a table is based on the estimated number of rows selected by the  WHERE  clause according to whichever access method the optimizer chooses. Condition filtering enables the optimizer to use other relevant conditions in the  WHERE  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  WHERE  clause that can filter (further restrict) the estimate for qualifying rows passed to the next table. A condition contributes to the filtering estimate only if: In  EXPLAIN  output, the  rows  column indicates the row estimate for the chosen access method, and the  filtered  column reflects the effect of condition filtering.  filtered  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. 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  rows  and  filtered  values. That is, the prefix row count is the estimated row count, reduced by the estimated filtering effect. For example, if  rows  is 1000 and  filtered  is 20%, condition filtering reduces the estimated row count of 1000 to a prefix row count of 1000 × 20% = 1000 × .2 = 200. Consider the following query: Suppose that the data set has these characteristics: employee.first_name = ’John’ 1675 •8 rows satisfy this condition on  employee.first_name : •The  employee  table has an index on  first_name . •Both tables have an index on  dept_no . •The  department  table has 12 rows. •The  employee  table has 1024 rows. •It was not already taken into account by the access method. •It depends on a constant value or values from earlier tables in the join sequence. •It refers to the current table. 8.2.1.13 Condition Filtering SELECT * FROM employee JOIN department ON employee.dept_no = department.dept_no WHERE employee.first_name = ’John’ AND employee.hire_date BETWEEN ’2018-01-01’ AND ’2018-06-01’;