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
WHERE sales_person = 'LUCCHESSI'
GROUP BY sales_person)
v1(a, b)
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 evaluated
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
pushdown is: 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, 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 pushdown transformation includes predicates that reference multiple
tables from an underlying join.