Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Join Predicate Pushing and Group By

with 5 comments

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. :)

About these ads

Written by Dion Cho

April 12, 2009 at 2:28 pm

5 Responses

Subscribe to comments with RSS.

  1. typo:

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

    radino

    April 12, 2009 at 8:59 pm

  2. @radino

    Fixed. Thanks for pointing it out.

    Dion Cho

    April 12, 2009 at 9:39 pm

  3. I have also found another typo where the query creating tables. Semicolon character might have been missed between two queries. :-)

    I always thank you for your clear technical explaination.


    UKJA@ukja102> create table t1(c1, c2)
    2 as select level, mod(level, 100)
    3 from dual
    4 connect by level ; // <= semicolon?
    create table t2(c1, c2, c3)
    2 as select mod(level, 4000), level, level
    3 from dual
    4 connect by level alter table t1 add constraint pk_t1 primary key (c1);

    phlow

    April 13, 2009 at 6:07 am

  4. @phlow

    Fixed. It’s always “<” character problem.

    Dion Cho

    April 13, 2009 at 8:57 am

  5. […] раз, не зависит и не меняет план своего выполнения (механизм join predicate push-down не работает для обзоров с GROUP BY в…) в зависимости от условий основного запроса (query predicates) […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 61 other followers

%d bloggers like this: