An SQL statement that references a view can also include a predicate.
Consider the view v2 (a,b):
CREATE VIEW v2(a,b) AS
SELECT sales_person, MAX(sales)
FROM Sales
GROUP BY sales_person
The following statement references the view and includes a predicate:
SELECT *
FROM v2
WHERE a = 'LUCCHESSI'
When transforms that statement by first transforming the view
into a derived table, it places the predicate at the top level of the new
query, outside the scope of the derived table:
SELECT a, b
FROM (SELECT sales_person, MAX(sales) FROM Sales
GROUP BY sales_person) v2(a, b) WHERE a = 'LUCCHESSI'
In the example in the preceding section (see ), 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.
However, if the source of all of the column references in a predicate is
a simple column reference in the underlying view or
table, is able to push the predicate down to the underlying view. Pushing down means that the
qualification described by the predicate can be evaluation when the view is
being evaluated. In our example, the column reference in the outer predicate, a, in the underlying view is a simple column reference to the underlying base table.
So the final transformation of this statement after predicate push-down is:
SELECT a, b
FROM (SELECT sales_person, MAX(sales) from Sales
WHERE city_id = 2 GROUP BY city_id) v1(a, b)
Without the transformation, 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, 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 push-down transformation includes predicates that reference multiple
tables from an underlying join.