Re: Partition way join
Date: Thu, 7 Feb 2008 12:05:24 -0000
Message-ID: <Uv6dnQVrlLnqaTfanZ2dneKdnZydnZ2d@bt.com>
<maks71_at_gmail.com> wrote in message
news:463674d0-c8ff-42ef-b4e2-eba5c6abd4c0_at_e23g2000prf.googlegroups.com...
What should one be looking to confirm if partition-wise join occurring or not..
- When no parallelism used
- 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")
Serial P/W join:
The hash join is the child of the partitioning decision.
Your interpretation was correct
| 3 | PARTITION LIST ALL
|* 4 | HASH JOIN
Serial non-P/W join
Two partitioning decisions are child operations to the hash join
|* 4 | HASH JOIN
| 5 | PARTITION LIST ALL
...
| N | PARTITION LIST ALL
Estimates of rows and size are for OUTPUTs not for INPUTs.
Your parallel plan is too messy to read, so I'm not going to try to think about why the partition-wise join is not taken. It is possible that the ANSI syntax is somehow interfering with the parallel transformation, however.
To force the p/w join, you need hints like:
select
/*
ordered
use_hash(cm_subscription) no_swap_join_inputs(cm_subscription)
full(cm_driver)
full(cm_subscription)
pq_distribute(cm_subscription, none, none)
*/
...
from cm_driver, cm_subscription
...
You may have some problems getting these hints into your ANSI style code.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Thu Feb 07 2008 - 06:05:24 CST