Partition way join
From: <maks71_at_gmail.com>
Date: Fri, 1 Feb 2008 15:28:20 -0800 (PST)
Message-ID: <8ba82692-04f5-44a9-ab75-d893eea2cc2d@d4g2000prg.googlegroups.com>
| Id | Operation |
Date: Fri, 1 Feb 2008 15:28:20 -0800 (PST)
Message-ID: <8ba82692-04f5-44a9-ab75-d893eea2cc2d@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)
Received on Fri Feb 01 2008 - 17:28:20 CST