ASA SQL User's Guide
  Query Optimization and Execution
    Semantic query transformations
      Types of semantic transformations

Predicate pushdown into GROUPed or UNION views

It is quite common for queries to restrict the result of a view so that only a few of the records are returned. In cases where the view contains GROUP BY or UNION, it would be preferable for the server to only compute the result for the desired rows.

Example 

Suppose we have the view product_summary defined as

CREATE VIEW product_summary( product_id, num_orders, total_qty) as
SELECT prod_id, count(*), sum( quantity )
FROM sales_order_items
GROUP BY prod_id

which returns, for each product ordered, a count of the number of orders that include it, and the sum of the quantities ordered over all of the orders. Now consider the following query over this view:

SELECT *
FROM product_summary
WHERE product_id = 300

which restricts the output to that for product id 300. The query and the query from the view could be combined into one semantically equivalent SELECT statement, namely:

SELECT prod_id, count(*), sum( quantity )
FROM sales_order_items
GROUP BY prod_id
HAVING prod_id = 300.

A naive execution plan for this query would involve computing the aggregates for each product, and then restricting the result to only the single row for product ID 300. However, the HAVING predicate on the product_id column can be pushed into the query's WHERE clause since it is a grouping column, yielding

SELECT prod_id, count(*), sum( quantity )
FROM sales_order_items
WHERE prod_id = 300
GROUP BY prod_id

which significantly reduces the computation required. If this predicate is sufficiently selective, the optimizer could now use an index on prod_id to quickly retrieve only those rows for product 300, rather than sequentially scanning the sales_order_items table.

The same optimization is also used for views involving UNION or UNION ALL.