Skip to Main Content

Join predicate pushing

Sekar_BLUE4EVERApr 18 2018 — edited Apr 19 2018

Hi ,

     I have  a complex query which joins three tables . Initially it joins two tables A and B using Hash join and then it joins the result with table C . But the weird thing is the join condition on table A and B gets pushed up and is only donw after the join operaion on table C .Since the tables are big in volume this fills up the temporary tablespace. How can we avoid join opeartion getting pushed up?

I have tried no_push_pred and no_push_subq hints but they seem not to help in this case.

------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name                      | E-Rows |  OMem |  1Mem | Used-Mem |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                           |        |       |       |          |

|*  1 |  HASH JOIN OUTER                   |                           |      2 |   718K|   718K|  169K (0)|

|   2 |   VIEW                             |                           |      2 |       |       |          |

|   3 |    CONCATENATION                   |                           |        |       |       |          |

|*  4 |     HASH JOIN                      |                           |      1 |   715K|   715K|  187K (0)|

|   5 |      NESTED LOOPS OUTER            |                           |      1 |       |       |          |

|*  6 |       FILTER                       |                           |        |       |       |          |

|   7 |        NESTED LOOPS OUTER          |                           |      1 |       |       |          |

|*  8 |         TABLE ACCESS BY INDEX ROWID| TABLE_A                   |      1 |       |       |          |

|*  9 |          INDEX RANGE SCAN          | IDX_TABLE_A_11            |      1 |       |       |          |

|* 10 |         TABLE ACCESS BY INDEX ROWID| TABLE_B                   |      1 |       |       |          |

|* 11 |          INDEX RANGE SCAN          | IDX_TABLE_B_51            |      1 |       |       |          |

|* 12 |       TABLE ACCESS FULL            | TABLE_C                   |      1 |       |       |          |

|* 13 |      TABLE ACCESS FULL             | TABLE_D                   |      1 |       |       |          |

|* 14 |     HASH JOIN OUTER                |                           |      1 |   707K|   707K|  187K (0)|

|* 15 |      FILTER                        |                           |        |       |       |          |

|* 16 |       HASH JOIN OUTER              |                           |      1 |   726K|   726K|  168K (0)|

|* 17 |        HASH JOIN                   |                           |      1 |   796K|   796K|  175K (0)|

|* 18 |         TABLE ACCESS FULL          | TABLE_A                   |      1 |       |       |          |

|* 19 |         TABLE ACCESS FULL          | TABLE_D                   |      1 |       |       |          |

|  20 |        TABLE ACCESS BY INDEX ROWID | TABLE_B                   |      3 |       |       |          |

|* 21 |         INDEX RANGE SCAN           | IDX_TABLE_B_51            |      3 |       |       |          |

|* 22 |      TABLE ACCESS FULL             | TABLE_C                   |   3707K|       |       |          |

|* 23 |   TABLE ACCESS FULL                | TABLE_E                   |    810K|       |       |          |

------------------------------------------------------------------------------------------------------------

Here in Operation 17 hash join there is a condition saying TABLE_A.col1=TABLE_D.col7 but the operation doesnt take place in ID 17 and it is postponed to ID 15 i.e the FILTER operaion because of some OR clauses in joins. Since the filetering on the jloin is delayed the number of rows returned are high and the next hash join fills up the temporary tablespace.

Is there a way to stop the join condition from being pushed or delayed?

Thanks

Comments
Post Details
Added on Apr 18 2018
11 comments
311 views