Oracle partition wise join

Sekar_BLUE4EVER

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

SeánMacGC

Sekar_BLUE4EVER wrote: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. 

On what are you basing this observation, ie, why do you believe that partition wise joins are not in operation when parallelized?

Sekar_BLUE4EVER

This links shows a sample plan in case parallel partition wise joins are used . https://docs.oracle.com/database/121/VLDBG/GUID-36B5A01D-849E-44A3-B6D7-2B82DD23DB73.htm And its completely of different format to what I get

SeánMacGC

Sekar_BLUE4EVER wrote:This links shows a sample plan in case parallel partition wise joins are used . https://docs.oracle.com/database/121/VLDBG/GUID-36B5A01D-849E-44A3-B6D7-2B82DD23DB73.htm And its completely of different format to what I get 

That's not reliable -- you're comparing two different versions of the optimizer to start with, and you're not comparing like with like (you don't have as much info in your explain plans), and there's nothing I can see in your parallel plan to demonstrate that partition-wise joins are not in play.

JohnWatson2

You can try hacking your statistics. For example, this gives me a parallel partition wise join:

orclx> exec dbms_stats.delete_table_stats(user,'t1h')PL/SQL procedure successfully completed.orclx> exec dbms_stats.delete_table_stats(user,'t2h')PL/SQL procedure successfully completed.orclx> alter session set optimizer_dynamic_sampling=0;Session altered.orclx> set autot trace exporclx> select /*+ parallel(2) */ count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x = t2.x;Execution Plan----------------------------------------------------------Plan hash value: 1341648614-----------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |-----------------------------------------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT          |          |    1 |    52 |  811  (1)| 00:00:01 |      |      |        |      |            ||  1 |  SORT AGGREGATE          |          |    1 |    52 |            |          |      |      |        |      |            ||  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            ||  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    52 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  ||  4 |    SORT AGGREGATE        |          |    1 |    52 |            |          |      |      |  Q1,00 | PCWP |            ||  5 |      PX PARTITION HASH ALL|          |  328K|    16M|  811  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            ||*  6 |      HASH JOIN          |          |  328K|    16M|  811  (1)| 00:00:01 |      |      |  Q1,00 | PCWP |            ||  7 |        TABLE ACCESS FULL  | T1H      |  328K|  8345K|  404  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            ||  8 |        TABLE ACCESS FULL  | T2H      |  328K|  8345K|  404  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |-----------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  6 - access("T1"."X"="T2"."X")Note-----  - Degree of Parallelism is 2 because of hintorclx>

Sekar_BLUE4EVER

The plan which i get doesnt have PX PARTITION HASH ALL which means that the partition wise join is not used . Its explained in the oracle blog here https://blogs.oracle.com/datawarehousing/partition-wise-joins-ii

SeánMacGC

Sekar_BLUE4EVER wrote:The plan which i get doesnt have PX PARTITION HASH ALL which means that the partition wise join is not used . Its explained in the oracle blog here https://blogs.oracle.com/datawarehousing/partition-wise-joins-ii

OK, have you now, and some more about it here: https://blogs.oracle.com/datawarehousing/partition-wise-joins

Sekar_BLUE4EVER

SM Join  SM cost: 116.27     (partition-wise join)

Thanks. I tried enabling 10053 trace and the optimizer doesnt even consider partition-wise-join in the trace where as without parallel hint I can clearly see that the optimizer is considering the PWJ and using it at the end. Could there be some optimizer parameters which are affecting this?

AndrewSayer

It seems to do the expected PWJ when you hint for a DOP equal to the number of partitions:

sql>explain plan for

  2  select /*+parallel(4)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x= t2.x;

Explained.

PLAN_TABLE_OUTPUT

________________________________________________________________________________________________________________________________________

Plan hash value: 1341648614

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

| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

|  0 | SELECT STATEMENT          |          |    1 |    10 |    76  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE          |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    10 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE        |          |    1 |    10 |            |          |      |      |  Q1,00 | PCWP |            |

|  5 |      PX PARTITION HASH ALL|          | 59998 |  585K|    76  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|*  6 |      HASH JOIN          |          | 59998 |  585K|    76  (0)| 00:00:01 |      |      |  Q1,00 | PCWP |            |

|  7 |        TABLE ACCESS FULL  | T1H      | 29999 |  292K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  8 |        TABLE ACCESS FULL  | T2H      | 59998 |  585K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

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

Query Block Name / Object Alias (identified by operation id):

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

  1 - SEL$1

  7 - SEL$1 / [email protected]$1

  8 - SEL$1 / [email protected]$1

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      PQ_DISTRIBUTE(@SEL$1 "T2"@SEL$1 NONE NONE)

      USE_HASH(@SEL$1 "T2"@SEL$1)

      LEADING(@SEL$1 "T1"@SEL$1 "T2"@SEL$1)

      FULL(@SEL$1 "T2"@SEL$1)

      FULL(@SEL$1 "T1"@SEL$1)

      OUTLINE_LEAF(@SEL$1)

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

  6 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 4 because of hint

If I double that to 8, I get:

sql>explain plan for

  2  select /*+parallel(8)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x= t2.x;

Explained.

PLAN_TABLE_OUTPUT

_____________________________________________________________________________________________________________________________________________

Plan hash value: 3645041885

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

| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

|  0 | SELECT STATEMENT              |          |    1 |    10 |    38  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE                |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR              |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)        | :TQ10001 |    1 |    10 |            |          |      |      |  Q1,01 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE            |          |    1 |    10 |            |          |      |      |  Q1,01 | PCWP |            |

|*  5 |      HASH JOIN                |          | 59998 |  585K|    38  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  6 |      PART JOIN FILTER CREATE  | :BF0000  | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  7 |        PX RECEIVE              |          | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  8 |        PX SEND BROADCAST LOCAL| :TQ10000 | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,00 | P->P | BCST LOCAL |

|  9 |          PX BLOCK ITERATOR    |          | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|  10 |          TABLE ACCESS FULL    | T1H      | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  11 |      PX BLOCK ITERATOR        |          | 59998 |  585K|    19  (0)| 00:00:01 |:BF0000|:BF0000|  Q1,01 | PCWC |            |

|  12 |        TABLE ACCESS FULL      | T2H      | 59998 |  585K|    19  (0)| 00:00:01 |:BF0000|:BF0000|  Q1,01 | PCWP |            |

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

Query Block Name / Object Alias (identified by operation id):

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

  1 - SEL$1

  10 - SEL$1 / [email protected]$1

  12 - SEL$1 / [email protected]$1

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      PQ_MAP(@SEL$1 "T2"@SEL$1)

      PQ_DISTRIBUTE(@SEL$1 "T2"@SEL$1 BROADCAST NONE)

      USE_HASH(@SEL$1 "T2"@SEL$1)

      LEADING(@SEL$1 "T1"@SEL$1 "T2"@SEL$1)

      FULL(@SEL$1 "T2"@SEL$1)

      FULL(@SEL$1 "T1"@SEL$1)

      OUTLINE_LEAF(@SEL$1)

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

  5 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 8 because of hint

58 rows selected.

Note the different distribution method chosen. You can hint for the initial one though:

sql>explain plan for

  2  select /*+parallel(8) PQ_DISTRIBUTE(@SEL$1 "T2"@SEL$1 NONE NONE)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x

Explained.

PLAN_TABLE_OUTPUT

________________________________________________________________________________________________________________________________________

Plan hash value: 1341648614

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

| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

|  0 | SELECT STATEMENT          |          |    1 |    10 |    38  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE          |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    10 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE        |          |    1 |    10 |            |          |      |      |  Q1,00 | PCWP |            |

|  5 |      PX PARTITION HASH ALL|          | 59998 |  585K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|*  6 |      HASH JOIN          |          | 59998 |  585K|    38  (0)| 00:00:01 |      |      |  Q1,00 | PCWP |            |

|  7 |        TABLE ACCESS FULL  | T1H      | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  8 |        TABLE ACCESS FULL  | T2H      | 59998 |  585K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

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

Query Block Name / Object Alias (identified by operation id):

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

  1 - SEL$1

  7 - SEL$1 / [email protected]$1

  8 - SEL$1 / [email protected]$1

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      PQ_DISTRIBUTE(@SEL$1 "T2"@SEL$1 NONE NONE)

      USE_HASH(@SEL$1 "T2"@SEL$1)

      LEADING(@SEL$1 "T1"@SEL$1 "T2"@SEL$1)

      FULL(@SEL$1 "T2"@SEL$1)

      FULL(@SEL$1 "T1"@SEL$1)

      OUTLINE_LEAF(@SEL$1)

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

  6 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 8 because of hint

53 rows selected.

Sekar_BLUE4EVER

Thanks Andrew, i tried it and yes I get the same behaviour as well. But it doesnt make much sense if PWJ is only going to be used when the DOP is equal to the number of partitions. Could it be a bug ? What would be the problem in using PWJ when using DOP as 2 for tables with 4 partitions ? Couldn't it do PWJ 2 time once for P1 and P2 and the second time around for P3 and P4?

SUPRIYO DEY

without using hint even you can achieve the same. But you need to enable parallel in table level. Which i don't find in your case.

please look at the demo:-

SQL> alter table t1h parallel;

Table altered.

SQL>  alter table t2h parallel;

Table altered.

SQL> select count(t1.y), count(t2.y)    from t1h t1,t2h t2     where t1.x= t2.x;

Execution Plan

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

Plan hash value: 593443938

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

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

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time

     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

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

|   0 | SELECT STATEMENT          |          |     1 |    52 |    29   (0)| 00:0

0:01 |       |       |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    52 |            |

     |       |       |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |

     |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |    52 |            |

     |       |       |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    52 |            |

     |       |       |  Q1,00 | PCWP |            |

|   5 |      PX PARTITION HASH ALL|          | 26732 |  1357K|    29   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWC |            |

|*  6 |       HASH JOIN           |          | 26732 |  1357K|    29   (0)| 00:0

0:01 |       |       |  Q1,00 | PCWP |            |

|   7 |        TABLE ACCESS FULL  | T2H      | 26731 |   678K|    14   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

|   8 |        TABLE ACCESS FULL  | T1H      | 26833 |   681K|    14   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

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

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

Predicate Information (identified by operation id):

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

   6 - access("T1"."X"="T2"."X")

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

         57  recursive calls

          0  db block gets

        514  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         11  sorts (memory)

          0  sorts (disk)

          1  rows processed

in this case automatic degree of parallelism is used by Oracle.

even if you go for parallel hint of DOP 2 still it happen

SQL> select /*+ parallel(2) */ count(t1.y), count(t2.y)    from t1h t1,t2h t2     where t1.x = t2.x;

Execution Plan

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

Plan hash value: 593443938

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

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

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time

     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

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

|   0 | SELECT STATEMENT          |          |     1 |    52 |    57   (0)| 00:0

0:01 |       |       |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    52 |            |

     |       |       |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |

     |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |    52 |            |

     |       |       |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    52 |            |

     |       |       |  Q1,00 | PCWP |            |

|   5 |      PX PARTITION HASH ALL|          | 26732 |  1357K|    57   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWC |            |

|*  6 |       HASH JOIN           |          | 26732 |  1357K|    57   (0)| 00:0

0:01 |       |       |  Q1,00 | PCWP |            |

|   7 |        TABLE ACCESS FULL  | T2H      | 26731 |   678K|    29   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

|   8 |        TABLE ACCESS FULL  | T1H      | 26833 |   681K|    28   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

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

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

Predicate Information (identified by operation id):

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

   6 - access("T1"."X"="T2"."X")

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Degree of Parallelism is 2 because of hint

Statistics

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

         47  recursive calls

          0  db block gets

        514  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         11  sorts (memory)

          0  sorts (disk)

          1  rows processed

From the DOC its mentioned that

https://docs.oracle.com/cd/B28359_01/server.111/b32024/part_avail.htm#CIHCDBIF

Considerations for full partition-wise joins also apply to partial partition-wise joins:

• The degree of parallelism does not need to equal the number of partitions. In Figure 4-3, the query executes with two sets of 16 query servers. In this case, Oracle assigns 1 partition to each query server of the second set. Again, the number of partitions should always be a multiple of the degree of parallelism.

Sekar_BLUE4EVER

Strange enough I replicated the exact same steps as you have given but it doesnt work for me . I tried enabling parallel 2 for the tables and the result is the same (PWJ not used). The only case when PWJ was used was when I set parallel for the tables to same as number of partitions. I am  testing it in version 11.2.0.3 and 11.2.0.1  and the result is the same. Practically it wouldnt be possible to have the same number of parallel processes as number of partitions. Can you tell me the version you tested on?

SUPRIYO DEY

11.2.0.4

as per doc

Considerations for full partition-wise joins also apply to partial partition-wise joins:

• The degree of parallelism does not need to equal the number of partitions. In Figure 4-3, the query executes with two sets of 16 query servers. In this case, Oracle assigns 1 partition to each query server of the second set. Again, the number of partitions should always be a multiple of the degree of parallelism.

Mohamed Houri

Sekar_BLUE4EVER wrote:Thanks Andrew, i tried it and yes I get the same behaviour as well. But it doesnt make much sense if PWJ is only going to be used when the DOP is equal to the number of partitions. Could it be a bug ? What would be the problem in using PWJ when using DOP as 2 for tables with 4 partitions ? Couldn't it do PWJ 2 time once for P1 and P2 and the second time around for P3 and P4? 

You know that "partition-wise" join has been used when you see that the partition operation is above the join operation.

But one important requirement for the partition-wise join to occur is that it requires from Oracle to read the partitioned object via Partition granule i.e. you see PX PARTITION in the execution plan. If a Block granule is used (PX BLOCK ITERATOR) instead of Partition granule (PX PARTITION) then partition wise join will not kick in.

Best regards

Mohamed Houri