Database Reference
In-Depth Information
In the following example, based on the
select_list_pruning.sql
script, notice how the subquery references
two columns (
n2
and
n3
) that aren't referenced in the main query:
SELECT n1
FROM (SELECT n1, n2, n3
FROM t)
Because the two columns
n2
and
n3
are unnecessary, select list pruning removes them and produces the
following query:
SELECT n1
FROM (SELECT n1
FROM t)
With view merging, the query can be simplified further. As a result, the following query is produced:
SELECT n1
FROM t
Predicate Push Down
The purpose of
predicate push down
is to push predicates inside views or inline views that can't be merged. The
predicates that can be pushed have to be contained in the query block that contains the unmergeable view or inline
view. There are three main reasons for applying this query transformation:
•
To enable additional access paths (typically index scans)
•
To enable additional join methods and/or join orders
•
To ensure that predicates are applied as soon as possible, thereby avoiding unnecessary
processing
Predicate push down has two subcategories:
filter push down
and
join predicate push down
. The difference
between the two is given by the type of predicates they work on.
Filter Push Down
The purpose of
filter push down
is to push restrictions (filtering conditions) inside views or inline views that can't
be merged. This is a heuristic-based query transformation. Be careful—this query transformation doesn't push join
conditions. Pushing join conditions is done by the query transformation presented in the next section.
The following example is based on the
filter_push_down.sql
script. The
UNION
set operator prevents the inline
view from being merged with the top-level query:
SELECT *
FROM (SELECT *
FROM t1
UNION
SELECT *
FROM t2)
WHERE id = 1
