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.