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
<<code:sql>>
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 <</sql>>
The naive way to execute the above is to 1. Compute the OCT_TOTALS contents (for all customers). 2. 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.