Basics of Join Predicate Pushdown in Oracle
- by Maria Colgan
Happy New Year to all of our readers! We hope you all had a great holiday season. We start the new year by continuing our series on Optimizer transformations. This time it is the turn of Predicate Pushdown. I would like to thank Rafi Ahmed for the content of this blog.Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with other tables using three methods: hash, nested loop, and sort-merge joins.
Introduction
The join predicate pushdown (JPPD) transformation allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. In the join predicate pushdown transformation, the view remains a separate query block, but it contains the join predicate, which is pushed down from its containing query block into the view. The view thus becomes correlated and must be evaluated for each row of the outer query block. These pushed-down join predicates, once inside the view, open up new index access paths on the base tables inside the view; this allows the view to be joined with index-based nested-loop join method, thereby enabling the optimizer to select an efficient execution plan. The join predicate pushdown transformation is not always optimal. The join predicate pushed-down view becomes correlated and it must be evaluated for each outer row; if there is a large number of outer rows, the cost of evaluating the view multiple times may make the nested-loop join suboptimal, and therefore joining the view with hash or sort-merge join method may be more efficient.
The decision whether to push down join predicates into a view is determined by evaluating the costs of the outer query with and without the join predicate pushdown transformation under Oracle's cost-based query transformation framework.
The join predicate pushdown transformation applies to both non-mergeable views and mergeable views and to pre-defined and inline views as well as to views generated internally by the optimizer during various transformations. The following shows the types of views on which join predicate pushdown is currently supported.
UNION ALL/UNION view
Outer-joined view
Anti-joined view
Semi-joined view
DISTINCT view
GROUP-BY view
Examples
Consider query A, which has an outer-joined view V. The view cannot be merged, as it contains two tables, and the join between these two tables must be performed before the join between the view and the outer table T4. A:
SELECT T4.unique1, V.unique3
FROM T_4K T4,
(SELECT T10.unique3, T10.hundred, T10.ten
FROM T_5K T5, T_10K T10
WHERE T5.unique3 = T10.unique3) VWHERE T4.unique3 = V.hundred(+) AND T4.ten = V.ten(+) AND T4.thousand = 5;
The following shows the non-default plan for query A generated by disabling join predicate pushdown.
When query A undergoes join predicate pushdown, it yields query B. Note that query B is expressed in a non-standard SQL and shows an internal representation of the query.
B:
SELECT T4.unique1, V.unique3
FROM T_4K T4, (SELECT T10.unique3, T10.hundred, T10.ten
FROM T_5K T5, T_10K T10
WHERE T5.unique3 = T10.unique3 AND
T4.unique3 = V.hundred(+) AND T4.ten = V.ten(+)) V
WHERE T4.thousand = 5;
The execution plan for query B is shown below.
In the execution plan BX, note the keyword 'VIEW PUSHED PREDICATE' indicates that the view has undergone the join predicate pushdown transformation. The join predicates (shown here in red) have been moved into the view V; these join predicates open up index access paths thereby enabling index-based nested-loop join of the view. With join predicate pushdown, the cost of query A has come down from 62 to 32.
As mentioned earlier, the join predicate pushdown transformation is cost-based, and a join predicate pushed-down plan is selected only when it reduces the overall cost.
Consider another example of a query C, which contains a view with the UNION ALL set operator.C:
SELECT R.unique1, V.unique3
FROM T_5K R,
(SELECT T1.unique3, T2.unique1+T1.unique1
FROM T_5K T1, T_10K T2
WHERE T1.unique1 = T2.unique1
UNION ALL
SELECT T1.unique3, T2.unique2
FROM G_4K T1, T_10K T2
WHERE T1.unique1 = T2.unique1) V
WHERE R.unique3 = V.unique3 and R.thousand < 1;
The execution plan of query C is shown below.
In the above, 'VIEW UNION ALL PUSHED PREDICATE' indicates that the UNION ALL view has undergone the join predicate pushdown transformation. As can be seen, here the join predicate has been replicated and pushed inside every branch of the UNION ALL view. The join predicates (shown here in red) open up index access paths thereby enabling index-based nested loop join of the view.
Consider query D as an example of join predicate pushdown into a distinct view. We have the following cardinalities of the tables involved in query D: Sales (1,016,271), Customers (50,000), and Costs (787,766).
D:
SELECT C.cust_last_name, C.cust_city
FROM customers C,
(SELECT DISTINCT S.cust_id
FROM sales S, costs CT
WHERE S.prod_id = CT.prod_id and CT.unit_price > 70) V
WHERE C.cust_state_province = 'CA' and C.cust_id = V.cust_id;
The execution plan of query D is shown below.
As shown in XD, when query D undergoes join predicate pushdown transformation, the expensive DISTINCT operator is removed and the join is converted into a semi-join; this is possible, since all the SELECT list items of the view participate in an equi-join with the outer tables. Under similar conditions, when a group-by view undergoes join predicate pushdown transformation, the expensive group-by operator can also be removed.
With the join predicate pushdown transformation, the elapsed time of query D came down from 63 seconds to 5 seconds.
Since distinct and group-by views are mergeable views, the cost-based transformation framework also compares the cost of merging the view with that of join predicate pushdown in selecting the most optimal execution plan.
Summary
We have tried to illustrate the basic ideas behind join predicate pushdown on different types of views by showing example queries that are quite simple. Oracle can handle far more complex queries and other types of views not shown here in the examples. Again many thanks to Rafi Ahmed for the content of this blog post.
