Pushing Predicates
|
December 2006 |
While teaching my class on Writing Optimal SQL I was asked by one of the attendees about the hint, and mechanism, for pushing join predicates. I had mentioned the /*+ push_pred */ hint in one of the slides as a relatively safe ‘strategy’ hint that invoked a feature without forcing a specific access method or join method on an object; but I had failed to give any examples of its use. Since the question came at the very end of the day, I promised to publish a note about the hint on my website – so here it is.
Pushing predicates relates to the way in which Oracle can do some optimisation with non-mergeable views. So, to demonstrate the effect, hints, and resource usage, I have used an example involving a join view, and an outer join in a way that makes the view non-mergeable. I have also made use of the 10g packaged function dbms_xplan.display_cursor() to make it easy to see what the effect is.
-- definition of the view:
create or replace view v1 as
select
t2.id1 id1_2,
t2.id2 id2_2,
t2.small_vc small_vc_2,
t2.padding padding_2,
t3.id1 id1_3,
t3.id2 id2_3,
t3.small_vc small_vc_3,
t3.padding padding_3
from
t2, t3
where
t3.id1 = t2.id1
and t3.id2 = t2.id2
;
-- The primary keys are (id1, id2) for the two tables,
-- and I have gathered stats with the cascade option,
-- and no histograms.
-- A sample query:
select
/*+
gather_plan_statistics
push_pred(v1)
*/
t1.small_vc,
v1.small_vc_2,
v1.small_vc_3
from
t1, v1
where
t1.n1 = 5
and v1.id1_2(+) = t1.id1
/
-- Acquiring, and displaying the execution plan, with statistics:
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID ggkujyuwf1z50, child number 0
-------------------------------------
select /*+ gather_plan_statistics push_pred(v1) */ t1.small_vc, v1.small_vc_2, v1.small_vc_3 from t1, v1
where t1.n1 = 5 and v1.id1_2(+) = t1.id1
Plan hash value: 3566562824
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS OUTER | | 1 | 50 | 50 |00:00:00.01 | 156 | | | |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 92 | | | |
| 3 | VIEW PUSHED PREDICATE | V1 | 10 | 1 | 50 |00:00:00.02 | 64 | | | |
|* 4 | HASH JOIN | | 10 | 1 | 50 |00:00:00.02 | 64 | 836K| 836K| 791K (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 5 | 50 |00:00:00.01 | 30 | | | |
|* 6 | INDEX RANGE SCAN | T2_PK | 10 | 5 | 50 |00:00:00.01 | 20 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T3 | 10 | 5 | 50 |00:00:00.01 | 34 | | | |
|* 8 | INDEX RANGE SCAN | T3_PK | 10 | 5 | 50 |00:00:00.01 | 22 | | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("T1"."N1"=5)
4 - access("T3"."ID1"="T2"."ID1" AND "T3"."ID2"="T2"."ID2")
6 - access("T2"."ID1"="T1"."ID1")
8 - access("T3"."ID1"="T1"."ID1")
With predicate pushing (which happened unhinted in this case) you can see the operation on line 3 showing View Pushed Predicate starting 10 times, because the tablescan of t1 on line 2 produced 10 rows (A-rows). So at run time, Oracle actually instantiated the view 10 times by pushing the value of the join column (id1) into the view definition – which you can see in the access predicates of lines 6 and 8.
If you repeat the exercise, but change the push_pred(v1) hint to no_push_pred(v1), you get the following output from dbms_xplan.
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID
1mpqv4fzx94nw, child number 0
-------------------------------------
select /*+ gather_plan_statistics no_push_pred(v1) */
t1.small_vc, v1.small_vc_2, v1.small_vc_3
from t1, v1
where t1.n1 =
5 and v1.id1_2(+) = t1.id1
Plan hash value: 3082429838
------------------------------------------------------------------------------------------------------------------
| Id |
Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers |
OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER |
| 1 | 50 |
50 |00:00:00.06 | 976 | 870K|
870K| 911K (0)|
|* 2 | TABLE ACCESS FULL | T1
| 1 | 10 |
10 |00:00:00.01 | 90 | |
| |
| 3 | VIEW | V1 |
1 | 25000 | 25000 |00:00:00.75 | 886 |
| | |
|* 4 | HASH JOIN |
| 1 | 25000 |
25000 |00:00:00.58 | 886
| 1522K|
1107K| 1689K (0)|
| 5 | TABLE ACCESS FULL| T2 |
1 | 25000 | 25000 |00:00:00.13 | 442 |
| | |
| 6 | TABLE ACCESS FULL| T3 | 1 |
25000 | 25000 |00:00:00.13 | 444 |
| | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
access("V1"."ID1_2"="T1"."ID1")
2 -
filter("T1"."N1"=5)
4 - access("T3"."ID1"="T2"."ID1" AND "T3"."ID2"="T2"."ID2")
As you can see, we get the same 10 rows at line 2; but this time we do not recreate the view result for each value returned. Instead we start the view operation line just once, joining t2 and t3 without the benefit of the precise access to data that predicate pushing gave us. Judging from the time, memory, and buffer usage, pushing predicates was a smart move in this case – doing several little jobs was more efficient than doing one large job because of the precision of the index that became available and the amount of data needed each time. But the optimizer doesn’t get it right every time, so it’s nice to know that you can over-ride its decisions.
There are some differences between 10g and 9i in the use of the push_pred hint. Testing against 9.2.0.8, I found that 9i would accept both push_pred and no_push_pred without any referenced views – interpreting the hint as “push predicates into every relevant view in the query”. In 10g, you had to use the hints explicitly for each view where you wanted to push predicates, and if the hints didn’t name a view then they had no effect. So if you are already using the push_pred or no_push_pred hints in 9i you may find that that are suddenly “ignored” in 10g for no obvious reason.
Footnote: if you have not come across the gather_plan_statistics hint before, this is new to 10g, and enables “rowsource execution statistics” to be gathered for a single query. For further details, read script $ORACLE_HOME/rdbms/admin/dbmsxpln.sql – which is the script that creates the package dbms_xplan.