Hi I am trying to implement partitioning in a complex environment where there are two many join operations in most of the SQL's. I was reading about the partition wise join operation where join on tables will be paralleled if the keys are the same and the boundaries are same as well.
I am trying to do a basic test case on a simple table if partition wise join works , while it works when there is no parallel operation the partition wise joins don't seem to work with parallel hint.
I am trying this in the following environment
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Test case:
create table t1h ( x int, y int )
partition by hash ( x )
partitions 4;
insert into t1h select rownum, rownum from dual connect by level < 30000;
create table t2h ( x int, y int )
partition by hash ( x )
partitions 4;
insert into t2h select rownum, rownum from dual connect by level < 30000;
Without parallel operation
select count(t1.y), count(t2.y) from t1h t1,t2h t2 where t1.x= t2.x
-------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | PARTITION HASH ALL | | 29999 | | | |
|* 3 | HASH JOIN | | 29999 | 1909K| 1608K| 1507K (0)|
| 4 | TABLE ACCESS FULL| T1H | 29999 | | | |
| 5 | TABLE ACCESS FULL| T2H | 29999 | | | |
-------------------------------------------------------------------------
With parallel operation
select /*+ parallel(2) */ count(t1.y), count(t2.y) from t1h t1,t2ht2 where t1.x = t2.x
----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | PX COORDINATOR | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | | | |
| 4 | SORT AGGREGATE | | 1 | | | |
|* 5 | HASH JOIN | | 29999 | 2010K| 1608K| 1785K (0)|
| 6 | PX BLOCK ITERATOR | | 29999 | | | |
|* 7 | TABLE ACCESS FULL | T1H | 29999 | | | |
| 8 | PX RECEIVE | | 29999 | | | |
| 9 | PX SEND BROADCAST | :TQ10000 | 29999 | | | |
| 10 | PX BLOCK ITERATOR | | 29999 | | | |
|* 11 | TABLE ACCESS FULL| T2H | 29999 | | | |
----------------------------------------------------------------------------------
I could not understand what the reason could be for the optimizer to ignore partition wise join when the parallel hint is supplied. According to documentation the plan should be something like this
https://docs.oracle.com/database/121/VLDBG/GUID-36B5A01D-849E-44A3-B6D7-2B82DD23DB73.htm
Can someone please explain me on how to make sure that partition wise join is getting used and how to force it ?
Thanks