JL Computer Consultancy

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.


Back to Index of Topics