Condition Pushdown into Derived Table Optimization
If a query uses a derived table (or a view), the first action that the query optimizer will attempt is to apply the derived-table-merge-optimization and merge the derived table into its parent select. However, that optimization is only applicable when the select inside the derived table has a join as the top-level operation. If it has a GROUP-BY, DISTINCT, or uses window functions, then derived-table-merge-optimization is not applicable.
In that case, Condition Pushdown optimization is applicable.
Introduction to Condition Pushdown
Consider an example
create view OCT_TOTALS as select customer_id, SUM(amount) as TOTAL_AMT from orders where order_date BETWEEN '2017-10-01' and '2017-10-31' group by customer_id; select * from OCT_TOTALS where customer_id=1
The naive way to execute the above is to
- Compute the OCT_TOTALS contents (for all customers).
- The, select the line with customer_id=1
This is obviously inefficient, if there are 1000 customers, then one will be doing up to 1000 times more work than necessary.
However, the optimizer can take the condition customer_id=1
and push it down into the OCT_TOTALS view.