Database Reference
In-Depth Information
Filter push down pushes the restriction (
id = 1
) inside the inline view and produces the following query. Not
only can the two tables now be accessed through an index, but it's also guaranteed that the sort operation required for
the
UNION
set operator processes as few rows as possible:
SELECT *
FROM (SELECT *
FROM t1
WHERE id = 1
UNION
SELECT *
FROM t2
WHERE id = 1)
Simple view merging then eliminates the top-level query block as well.
Join Predicate Push Down
The purpose of
join predicate push down
is to push join conditions inside views or inline views that can't be merged.
This is a cost-based query transformation.
The following example is based on the
join_predicate_push_down.sql
script. The
UNION
set operator prevents
the inline view from being merged with the top-level query. Notice that the inline view is the same (the tables have a
different name, though) as the one used in the example in the preceding section. In this case, though, the inline view
is joined to another table:
SELECT *
FROM t1, (SELECT *
FROM t2
UNION
SELECT *
FROM t3) t23
WHERE t1.id = t23.id
Join predicate push down pushes the join condition (
t1.id = t23.id
) inside the inline view and produces the
following query. This query shares the same advantages as the one described in the previous section (index accesses
enabled, and reduced amount of data to sort). In this specific case, the additional access paths also enable the query
optimizer to freely choose between all available join methods and join orders:
SELECT *
FROM t1, (SELECT *
FROM t2
WHERE t2.id = t1.id
UNION
SELECT *
FROM t3
WHERE t3.id = t1.id) t23
