Join Predicate Pushing and Group By

A couple of days ago, I got asked following question.

I have following query.

select t1.c1, t1.c2, v.*
from t1, (select c1, sum(c2), sum(c3) from t2 group by c1) v
where t1.c1 = v.c1(+) and t1.c2 in (1, 2)
;

The problem is that I have join predicate pushing disabled. Thus the pagination is hard to achieve. Why and any solution?

Okay, simple but interesting question.

1. What is join predicate pushing(a.k.a JPPD)?
JPPD means literally pushing the join predicate into the view. See following example.

UKJA@ukja116> explain plan for
  2  select
  3    t1.c1, t1.c2, v.*
  4  from t1,
  5  	 (select /*+ no_merge index(t2) */ c1, c2, c3
  6  	   from t2
  7  	   --group by c1
  8  	   ) v
  9  where
 10    t1.c1 = v.c1(+)
 11    and t1.c2 in (1, 2)
 12  ;

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |  2000 |
|   1 |  NESTED LOOPS OUTER           |       |  2000 |
|*  2 |   TABLE ACCESS FULL           | T1    |    80 |
|   3 |   VIEW PUSHED PREDICATE       |       |     1 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T2    |    25 |
|*  5 |     INDEX RANGE SCAN          | PK_T2 |    25 |
-------------------------------------------------------
                                                       
Predicate Information (identified by operation id):    
---------------------------------------------------    
                                                       
   2 - filter("T1"."C2"=1 OR "T1"."C2"=2)              
   5 - access("C1"="T1"."C1")                          

Here you can see that the join predicate(t1.c1 = v.c1(+)) is pushed into the inline view. This technique is extremely useful for pagination queries.

Note that JPPD works only with nested loop join. It’s very logical restriction.

2. When do we have JPPD enabled?
At the first step, Oracle tries to merge the view. This is called view merging. When the view merging is not possible, Oracle tries to push the predicates into the view. Oracle pushes not only simple predicates, but also join predicates. PUSH_PRED hint controls the join predicate pushing.

Oracle denies to push join predicate when the view has GROUP BY, DISTINCT expression or ANTI/SEMI join, just because it’s too complex to push the join predicate with those ones.

Fortunately, this restriction has gone with 11g. This is sometimes called extended JPPD and controlled by _optimizer_extend_jppd_view_types(whose default value is TRUE) parameter.

3. Your explanation is boring. Any clear test case?
For those who want live demo, not boring and dead verbal explanation(like me!), here goes a very simple demonstration.

#1. Create tables.

UKJA@ukja102> create table t1(c1, c2)
  2  as select level, mod(level, 100)
  3  from dual
  4  connect by level <= 4000
  5  ;

Table created.

UKJA@ukja102> create table t2(c1, c2, c3)
  2  as select mod(level, 4000), level, level
  3  from dual
  4  connect by level <= 100000
  5  ;

Table created.

UKJA@ukja102> alter table t1 add constraint pk_t1 primary key (c1);

Table altered.

UKJA@ukja102> alter table t2 add constraint pk_t2 primary key (c1, c2);

Table altered.

UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);

PL/SQL procedure successfully completed.

UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't2', no_invalidate=>false);

#2. Oracle 10g fails at JPPD with GROUP BY inside the view.

UKJA@ukja102> explain plan for
  2  select
  3    t1.c1, t1.c2, v.*
  4  from t1,
  5      (select /*+ no_merge index(t2) */ c1, sum(c2), max(c3)
  6        from t2
  7        group by c1) v
  8  where
  9    t1.c1 = v.c1(+)
 10    and t1.c2 in (1, 2)
 11  ;

----------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes |
----------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |    80 |  3600 |
|*  1 |  HASH JOIN OUTER               |       |    80 |  3600 |
|*  2 |   TABLE ACCESS FULL            | T1    |    80 |   480 |
|   3 |   VIEW                         |       |  4020 |   153K|
|   4 |    HASH GROUP BY               |       |  4020 | 52260 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T2    |   100K|  1269K|
|   6 |      INDEX FULL SCAN           | PK_T2 |   100K|       |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="V"."C1"(+))
   2 - filter("T1"."C2"=1 OR "T1"."C2"=2)                               

10053 trace simply says, “I cannot push join predicates because of GROUP BY!”.

JPPD:   Checking validity of push-down from SEL$1 (#1) to SEL$2 (#2)
JPPD:     JPPD bypassed: View contains a group by.

#3. But Oracle 11g has no such restriction!

UKJA@ukja116> explain plan for
  2  select
  3    t1.c1, t1.c2, v.*
  4  from t1,
  5      (select /*+ no_merge index(t2) */ c1, sum(c2), max(c3)
  6        from t2
  7        group by c1) v
  8  where
  9    t1.c1 = v.c1(+)
 10    and t1.c2 in (1, 2)
 11  ;

-----------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |    80 |  2560 |
|   1 |  NESTED LOOPS OUTER             |       |    80 |  2560 |
|*  2 |   TABLE ACCESS FULL             | T1    |    80 |   480 |
|   3 |   VIEW PUSHED PREDICATE         |       |     1 |    26 |
|*  4 |    FILTER                       |       |       |       |
|   5 |     SORT AGGREGATE              |       |     1 |    13 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T2    |    25 |   325 |
|*  7 |       INDEX RANGE SCAN          | PK_T2 |    25 |       |
-----------------------------------------------------------------
                                                                 
Predicate Information (identified by operation id):              
---------------------------------------------------              
                                                                 
   2 - filter("T1"."C2"=1 OR "T1"."C2"=2)                        
   4 - filter(COUNT(*)>0)                                        
   7 - access("C1"="T1"."C1")                                    

Note that Oracle chose NESTED LOOP OUTER join over HASH OUTER JOIN. JPPD works only with nested loop join!

#4. In Oracle 10g, you can emulate JPPD like this.

select
  c1, c2,
  (select sum(c2) from t2 where t2.c1 = t1.c1),
  (select sum(c3) from t2 where t2.c1 = t1.c1)
from 
  t1
where
  c2 in (1, 2)
;

-- or

select
  c1, c2,
  cursor(select sum(c2), sum(c3) from t2 where t2.c1 = t1.c1)
from 
  t1
where
  c2 in (1, 2)
;

It’s sometimes amazing how Oracle transforms the query. At the same time, it’s very disappointing why Oracle didn’t do it a bit earlier. :)

Advertisement