CREATE VIEW v2(a,b) AS SELECT sales_person, MAX(sales) FROM Sales GROUP BY sales_person
SELECT * FROM v2 WHERE a = 'LUCCHESSI'
SELECT a, b FROM (SELECT sales_person, MAX(sales) FROM Sales WHERE sales_person = 'LUCCHESSI' GROUP BY sales_person) v1(a, b)
In the example in the preceding section (see View flattening), Derby was able to flatten the derived table into the main SELECT, so the predicate in the outer SELECT could be evaluated at a useful point in the query. This is not possible in this example, because the underlying view does not satisfy all the requirements of view flattening.
SELECT a, b FROM (SELECT sales_person, MAX(sales) from Sales WHERE sales_person = 'LUCCHESSI' GROUP BY sales_person) v1(a, b)
Without the transformation, Derby would have to scan the entire table t1 to form all the groups, only to throw out all but one of the groups. With the transformation, Derby is able to make that qualification part of the derived table.
If there were a predicate that referenced column b, it could not be pushed down, because in the underlying view, column b is not a simple column reference.
Predicate pushdown transformation includes predicates that reference multiple tables from an underlying join.