Database Reference
In-Depth Information
Even though the previous SQL statement isn't valid (the
t1.id
column isn't visible inside the inline view), the
SQL engine can process something similar to it. To support such a query, as of version 12.1 lateral inline views are
available. For instance, the following is a valid query in version 12.1:
SELECT *
FROM t1,
lateral
(SELECT *
FROM t2
WHERE t2.id = t1.id
UNION
SELECT *
FROM t3
WHERE t3.id = t1.id) t23
Predicate Move Around
The purpose of
predicate move around
is to pull up, move across, and push down restrictions (filtering conditions)
inside views or inline views that can't be merged. Even though it's similar to predicate push down, this query
transformation can also move predicates across query blocks that aren't contained in each other. The main reasons
for applying this heuristic-based query transformation are enabling additional access paths (typically, index scans)
and making sure that predicates are applied as soon as possible.
The two inline views in the following example, based on the
predicate_move_around.sql
script, can't be merged
with the top-level query because of the
DISTINCT
operator. The first inline view contains a restriction on the column
(
n
) used as join condition between the two inline views (
t1.n = t2.n
):
SELECT t1.pad, t2.pad
FROM (SELECT DISTINCT n, pad
FROM t1
WHERE n = 1) t1,
(SELECT DISTINCT n, pad
FROM t2) t2
WHERE t1.n = t2.n
In this case, predicate move around is performed in three main steps:
1.
Pull up the restriction (
n = 1
) from the first inline view into the top-level query block.
2.
Apply transitivity between the restriction (
t1.n = 1
) and the join condition (
t1.n = t2.n
)
and generate a new predicate (
t2.n = 1
).
3.
Push the new predicate inside the second inline view.
As a result, predicate move around generates the following query. The predicate added in the second inline view
not only enables table
t2
access through an index, it also likely reduces the amount of data to be processed by the
DISTINCT
operator:
SELECT t1.pad, t2.pad
FROM (SELECT DISTINCT n, pad
FROM t1
WHERE n = 1) t1,
(SELECT DISTINCT n, pad
FROM t2
