Forum Stats

  • 3,732,979 Users
  • 2,246,665 Discussions
  • 7,856,452 Comments

Discussions

Oracle partition wise join

Sekar_BLUE4EVER
Sekar_BLUE4EVER Member Posts: 324 Blue Ribbon

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

Answers

  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited June 2018
    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
    Sekar_BLUE4EVER Member Posts: 324 Blue Ribbon
    edited June 2018

    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
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited June 2018
    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
    JohnWatson2 Member Posts: 4,238 Bronze Crown
    edited June 2018

    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
    Sekar_BLUE4EVER Member Posts: 324 Blue Ribbon
    edited June 2018

    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
  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited June 2018
    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
    Sekar_BLUE4EVER Member Posts: 324 Blue Ribbon
    edited June 2018
    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
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited June 2018

    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
    Sekar_BLUE4EVER Member Posts: 324 Blue Ribbon
    edited June 2018

    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
    SUPRIYO DEY Member Posts: 2,127 Silver Trophy
    edited June 2018

    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
    Sekar_BLUE4EVER Member Posts: 324 Blue Ribbon
    edited June 2018

    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
    SUPRIYO DEY Member Posts: 2,127 Silver Trophy
    edited June 2018

    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
    Mohamed Houri Member Posts: 1,193 Bronze Trophy
    edited June 2018

    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

This discussion has been closed.