Re: Partition way join

From: <maks71_at_gmail.com>
Date: Sun, 3 Feb 2008 14:56:09 -0800 (PST)
Message-ID: <463674d0-c8ff-42ef-b4e2-eba5c6abd4c0@e23g2000prf.googlegroups.com>


On Feb 2, 6:40 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <mak..._at_gmail.com> wrote in message
>
> news:8ba82692-04f5-44a9-ab75-d893eea2cc2d_at_d4g2000prg.googlegroups.com...
>
>
>
>
>
> > Can someone inspect the following SQL and tell me if Partition way
> > join is occurring here.. I suspect that its not occuring ( As I don't
> > see PX PARTITION HASH anywhere is the plan , per 10.2 doc ) but not
> > sure.  If its not occuring , can some one explain why?  All the tables
> > are equi-partitioned on same columns.. I'm running 10.2.0.3.
> > I aplogize for output is not  being in nice format .. I don't know how
> > to post that in a formatted way.. IF someone know, please let me know.
>
> > Thanks in advance for your answers.
>
> > CREATE TABLE temp1 NOLOGGING PARALLEL(DEGREE 6) AS SELECT DISTINCT
> > CM_COPY.CM_DRIVER.TRGT_KEY FROM ((CM_COPY.CM_DRIVER INNER JOIN
> > CM_COPY.CM_SUBSCRIPTION ON
> > (CM_COPY.CM_DRIVER.CUST_ACCT_KEY =
> > CM_COPY.CM_SUBSCRIPTION.CUST_ACCT_KEY   AND
> > CM_COPY.CM_DRIVER.ORG_PART_KEY =
> > CM_COPY.CM_SUBSCRIPTION.ORG_PART_KEY)) INNER JOIN
> > CM_COPY.V_CM_SERVICE_CODE ON (CM_COPY.CM_SUBSCRIPTION.SRVC_CD_KEY =
> > CM_COPY.V_CM_SERVICE_CODE.SRVC_CD_KEY  AND
> > CM_COPY.CM_SUBSCRIPTION.ORG_PART_KEY =
> > CM_COPY.V_CM_SERVICE_CODE.ORG_PART_KEY)) WHERE
> > (CM_COPY.V_CM_SERVICE_CODE.SRVC_CD_KEY > 0)
>
> > Plan hash value: 1934355852
>
> > ---------------------------------------------------------------------------­---------------------------------------------------------------------------­--------------------------------
> > | Id  | Operation                                       |
> > Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)|
> > Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
> > ---------------------------------------------------------------------------­---------------------------------------------------------------------------­--------------------------------
> > |   0 | CREATE TABLE STATEMENT
> > |                               |       |    |  |
> > 165K(100)|          |       |       |        |      |            |
> > |   1 |  PX COORDINATOR
> > |                               |       |    |  |
> > |          |       |       |        |      |            |
> > |   2 |   PX SEND QC (RANDOM)
> > | :TQ10004                      |  1265K|    84M|       |   165K  (2)|
> > 00:38:32 |       |       |  Q1,04 | P->S | QC (RAND)  |
> > |   3 |    LOAD AS SELECT
> > |                               |       |    |  |
> > |          |       |       |  Q1,04 | PCWP |            |
> > |   4 |     HASH UNIQUE
> > |                               |  1265K|    84M|   193M|   165K  (2)|
> > 00:38:32 |       |       |  Q1,04 | PCWP |            |
> > |   5 |      PX RECEIVE
> > |                               |  1265K|    84M|       |   165K  (2)|
> > 00:38:32 |       |       |  Q1,04 | PCWP |            |
> > |   6 |       PX SEND HASH
> > | :TQ10003                      |  1265K|    84M|       |   165K  (2)|
> > 00:38:32 |       |       |  Q1,03 | P->P | HASH       |
> > |*  7 |        HASH JOIN
> > |                               |  1265K|    84M|       |   165K  (2)|
> > 00:38:32 |       |       |  Q1,03 | PCWP |            |
> > |   8 |         PX RECEIVE
> > |                               |  2733K|   130M|       |   158K  (2)|
> > 00:37:02 |       |       |  Q1,03 | PCWP |            |
> > |   9 |          PX SEND BROADCAST LOCAL
> > | :TQ10002                      |  2733K|   130M|       |   158K  (2)|
> > 00:37:02 |       |       |  Q1,02 | P->P | BCST LOCAL |
> > |* 10 |           HASH JOIN
> > |                               |  2733K|   130M|       |   158K  (2)|
> > 00:37:02 |       |       |  Q1,02 | PCWP |            |
> > |  11 |            PX RECEIVE
> > |                               | 64750 |  2086K|       |  1297   (1)|
> > 00:00:19 |       |       |  Q1,02 | PCWP |            |
> > |  12 |             PX SEND BROADCAST LOCAL
> > | :TQ10001                      | 64750 |  2086K|       |  1297   (1)|
> > 00:00:19 |       |       |  Q1,01 | P->P | BCST LOCAL |
> > |* 13 |              HASH JOIN RIGHT OUTER
> > |                               | 64750 |  2086K|       |  1297   (1)|
> > 00:00:19 |       |       |  Q1,01 | PCWP |            |
> > |  14 |               PX RECEIVE
> > |                               |  2635 | 13175 |       |     2   (0)|
> > 00:00:01 |       |       |  Q1,01 | PCWP |            |
> > |  15 |                PX SEND BROADCAST
> > | :TQ10000                      |  2635 | 13175 |       |     2   (0)|
> > 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |
> > |  16 |                 PX BLOCK ITERATOR
> > |                               |  2635 | 13175 |       |     2   (0)|
> > 00:00:01 |       |       |  Q1,00 | PCWC |            |
> > |* 17 |                  INDEX FAST FULL SCAN           |
> > I_CM_HIERARCHY_STAND_CD_PK1   |  2635 | 13175 |       |     2   (0)|
> > 00:00:01 |       |       |  Q1,00 | PCWP |            |
> > |  18 |               PX PARTITION LIST ALL
> > |                               | 64750 |  1770K|       |  1295   (1)|
> > 00:00:19 |     1 |    33 |  Q1,01 | PCWC |            |
> > |* 19 |                TABLE ACCESS BY LOCAL INDEX ROWID|
> > CM_STANDARD_CODE_DENORM       | 64750 |  1770K|       |  1295   (1)|
> > 00:00:19 |     1 |    33 |  Q1,01 | PCWP |            |
> > |* 20 |                 INDEX RANGE SCAN                |
> > I_CM_STANDARD_CODE_DENORM_IE2 | 64854 |    |  |    35   (0)| 00:00:01
> > |     1 |    33 |  Q1,01 | PCWP |            |
> > |  21 |            PX BLOCK ITERATOR
> > |                               |   289M|  4696M|       |   156K  (1)|
> > 00:36:38 |     1 |    33 |  Q1,02 | PCWC |            |
> > |* 22 |             TABLE ACCESS FULL                   |
> > CM_SUBSCRIPTION               |   289M|  4696M|       |   156K  (1)|
> > 00:36:38 |     1 |    33 |  Q1,02 | PCWP |            |
> > |  23 |         PX BLOCK ITERATOR
> > |                               |    27M|   517M|       |  6387   (3)|
> > 00:01:30 |     1 |    33 |  Q1,03 | PCWC |            |
> > |* 24 |          TABLE ACCESS FULL                      |
> > CM_DRIVER                     |    27M|   517M|       |  6387   (3)|
> > 00:01:30 |     1 |    33 |  Q1,03 | PCWP |            |
> > ---------------------------------------------------------------------------­---------------------------------------------------------------------------­--------------------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> >   7 -
> > access("CM_DRIVER"."ORG_PART_KEY"="CM_SUBSCRIPTION"."ORG_PART_KEY" AND
> > "CM_DRIVER"."CUST_ACCT_KEY"="CM_SUBSCRIPTION"."CUST_ACCT_KEY")
> >  10 - access("CM_SUBSCRIPTION"."ORG_PART_KEY"="SC"."ORG_PART_KEY" AND
> > "CM_SUBSCRIPTION"."SRVC_CD_KEY"="SC"."CD_KEY")
> >  13 - access("SC"."STD_CD_KEY"="HI"."STD_CD_KEY")
> >  17 - access(:Z>=:Z AND :Z<=:Z)
> >  19 - filter("SC"."CD_KEY">0)
> >  20 - access("SC"."CD_TYP_CD"='SERVICE')
> >  22 - access(:Z>=:Z AND :Z<=:Z)
> >       filter("CM_SUBSCRIPTION"."SRVC_CD_KEY">0)
> >  24 - access(:Z>=:Z AND :Z<=:Z)
>
> The plan is not showing partition-wise joins. Lines 9, 12, and 15
> show 'PX SEND BROADCAST'. This means the optimizer has
> decided that three of your tables are "small" and can be hashed
> completely in memory by every single PX slave.
>
> This can be incredibly efficient, but can happen in unsuitable
> cases if the number of partitions involved is smaller than the
> degree of parallelism used.
>
> If you want to stop this happening, you can use the pq_distribute()
> hint - it's in the manuals - to control the way the tables are distributed.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -

Thank you Jonathan for the answer. a few f/wup questions.. (Sorry its little longer... )

What should one be looking to confirm if partition-wise join occurring or not..

  1. When no parallelism used
  2. When parallelism used

Oracle Doc. says, look for presence of "PX PARTITION <Partition Type> ALL" above the actual Hash join...not clear on what to look for when not using parallelism.

In following example,[FYI, I have removed the ref. to view from origional posted SQL for clarity ]. CM_DRIVER and CM_SUBSCRIPTION tables are LIST partitioned on ORA_PART_KEY and joined on same column with one additional column CUST_ACCT_KEY.

Size of CM_DRIVER is 1.6GB total w/ partition size ranging from 4MB-240MB.
Size of CM_SUBSCRIPTION is 40GB w/ partition size ranging from 64MB to 4 GB.
Total # of partitions are 33.

 Explain plan shown with PARALLEL & NOPARALLEL clause.

I think, w/o PARALLEL Clause is using Partition-wise(P-W) join (of course in serial mode) as indicated by Line#3 "PARTITION LIST ALL" above HASH JOIN with respective parition range in PSTART & PSTOP column. Is my interpretation correct?

However, I don't see any such indication in explain plan of one with the PARALLEL clause... Per my understanding (from 10g doc ) , it's not using P-W join and not sure why its not using one.. Am I interpreting this correctly? If so, Can you shed some lights as to why its not using P-W join....

Also, its not showing correct sizes of the table in explain plan. Showing 3GB(instead of 40G) for CM_SUBSCRIPTION and 517M (instead of 1.6G) in explain plan though rowcount is almost correct. Below are the stats.

TABLE_Name #ROWS Blocks
--------------- ------------ ------------ CM_SUBSCRIPTION 289,592,090 2,541,839 CM_DRIVER 27,102,910 102,162

  • W/O PARALLEL Clause.

explain plan for
CREATE TABLE UAC_7611_q1
NOLOGGING
 AS
SELECT DISTINCT CM_COPY.CM_DRIVER.TRGT_KEY FROM ((CM_COPY.CM_DRIVER
INNER JOIN CM_COPY.CM_SUBSCRIPTION
ON (CM_COPY.CM_DRIVER.CUST_ACCT_KEY =
CM_COPY.CM_SUBSCRIPTION.CUST_ACCT_KEY AND     CM_COPY.CM_DRIVER.ORG_PART_KEY =
CM_COPY.CM_SUBSCRIPTION.ORG_PART_KEY))); PLAN_TABLE_OUTPUT



Plan hash value: 1075960132
| Id  | Operation                | Name                  | Rows  |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
|   0 | CREATE TABLE STATEMENT   |                       |    10M|
296M|       |   966K  (3)| 03:45:34 |       |       |
|   1 |  LOAD AS SELECT          | UAC_7611_Q1           |
|       |       |            |       |  |       |
|   2 |   HASH UNIQUE            |                       |    10M|
296M|  5506M|   863K  (4)| 03:21:29 |       |       |
|   3 |    PARTITION LIST ALL    |                       |   134M|
3965M|       |   477K  (3)| 01:51:21 |     1 |    33 |
|*  4 |     HASH JOIN            |                       |   134M|
3965M|    25M|   477K  (3)| 01:51:21 |       |       |
|   5 |      TABLE ACCESS FULL   | CM_DRIVER             |    27M|
517M|       | 34582   (3)| 00:08:05 |     1 |    33 |
|   6 |      INDEX FAST FULL SCAN| I_CM_SUBSCRIPTION_IE1 |   289M|
3039M|       |   129K  (3)| 00:30:20 |     1 |    33 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   4 -
access("CM_DRIVER"."ORG_PART_KEY"="CM_SUBSCRIPTION"."ORG_PART_KEY" AND  

"CM_DRIVER"."CUST_ACCT_KEY"="CM_SUBSCRIPTION"."CUST_ACCT_KEY") 19 rows selected.

  • W/ PARALLEL Clause.

explain plan for
CREATE TABLE UAC_7611_q1
NOLOGGING
PARALLEL
 AS
SELECT DISTINCT CM_COPY.CM_DRIVER.TRGT_KEY FROM ((CM_COPY.CM_DRIVER
INNER JOIN CM_COPY.CM_SUBSCRIPTION
ON (CM_COPY.CM_DRIVER.CUST_ACCT_KEY =
CM_COPY.CM_SUBSCRIPTION.CUST_ACCT_KEY AND     CM_COPY.CM_DRIVER.ORG_PART_KEY =
CM_COPY.CM_SUBSCRIPTION.ORG_PART_KEY))); PLAN_TABLE_OUTPUT



Plan hash value: 2459914318
| Id  | Operation                   | Name            | Rows  | Bytes |
TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ
Distrib |
|   0 | CREATE TABLE STATEMENT      |                 |    10M|
296M|       |  3426   (3)| 00:00:48 |       |       |        |
|            |
|   1 |  PX COORDINATOR             |                 |       |
|       |            |          |       |       |        |
|            |
|   2 |   PX SEND QC (RANDOM)       | :TQ10003        |    10M|
296M|       |  3104   (3)| 00:00:44 |       |       |  Q1,03 | P->S |
QC (RAND)  |
|   3 |    LOAD AS SELECT           | UAC_7611_Q1     |       |
|       |            |          |       |       |  Q1,03 | PCWP
|            |
|   4 |     HASH UNIQUE             |                 |    10M|
296M|  5506M|  3104   (3)| 00:00:44 |       |       |  Q1,03 | PCWP
|            |
|   5 |      PX RECEIVE             |                 |   134M|
3965M|       |  3066   (2)| 00:00:43 |       |       |  Q1,03 | PCWP
|            |
|   6 |       PX SEND HASH          | :TQ10002        |   134M|
3965M|       |  3066   (2)| 00:00:43 |       |       |  Q1,02 | P->P |
HASH       |
|*  7 |        HASH JOIN BUFFERED   |                 |   134M|
3965M|       |  3066   (2)| 00:00:43 |       |       |  Q1,02 | PCWP
|            |
|   8 |         PX RECEIVE          |                 |    27M|
517M|       |   120   (3)| 00:00:02 |       |       |  Q1,02 | PCWP
|            |
|   9 |          PX SEND HASH LOCAL | :TQ10000        |    27M|
517M|       |   120   (3)| 00:00:02 |       |       |  Q1,00 | P->P |
HASH LOCAL |
|  10 |           PX BLOCK ITERATOR |                 |    27M|
517M|       |   120   (3)| 00:00:02 |     1 |    33 |  Q1,00 | PCWC
|            |
|  11 |            TABLE ACCESS FULL| CM_DRIVER       |    27M|
517M|       |   120   (3)| 00:00:02 |     1 |    33 |  Q1,00 | PCWP
|            |
|  12 |         PX RECEIVE          |                 |   289M|
3039M|       |  2936   (1)| 00:00:42 |       |       |  Q1,02 | PCWP
|            |
|  13 |          PX SEND HASH LOCAL | :TQ10001        |   289M|
3039M|       |  2936   (1)| 00:00:42 |       |       |  Q1,01 | P->P |
HASH LOCAL |
|  14 |           PX BLOCK ITERATOR |                 |   289M|
3039M|       |  2936   (1)| 00:00:42 |     1 |    33 |  Q1,01 | PCWC
|            |
|  15 |            TABLE ACCESS FULL| CM_SUBSCRIPTION |   289M|
3039M|       |  2936   (1)| 00:00:42 |     1 |    33 |  Q1,01 | PCWP
|            |
----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   7 -
access("CM_DRIVER"."ORG_PART_KEY"="CM_SUBSCRIPTION"."ORG_PART_KEY" AND "CM_DRIVER"."CUST_ACCT_KEY"="CM_SUBSCRIPTION"."CUST_ACCT_KEY") Received on Sun Feb 03 2008 - 16:56:09 CST

Original text of this message