-
Partition-Wise Join in different cases
Hi, all
Following is an example of partition-wise join in different cases, and leads to several question need your advice:
drop table pn;
drop table ps;
drop table ps2;
create table pn (u_id int, u_name varchar2(10)) partition by hash(u_id) partitions 4;
create table ps (u_id int, u_sales int) partition by hash(u_id) partitions 4;
create table ps2 (u_id int, u_sales int) partition by hash(u_id) partitions 4;
insert into pn values(1,'Calvin');
insert into pn values(2,' Jessica Qiu');
insert into ps values(1, 1000);
insert into ps values(2, 1000);
insert into ps2 values(1, 1000);
insert into ps2 values(2, 1000);
commit;
exec dbms_stats.set_table_stats('CALVIN', 'PN', numrows => 1000000000);
exec dbms_stats.set_table_stats('CALVIN', 'PS', numrows => 100000000);
commit;
--Query 1
explain plan for select pn.u_name, ps.u_sales from pn, ps where pn.u_id = ps.u_id;
@?/rdbms/admin/utlxplp
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000M| 56G| | 3623K (3)| 12:04:40 | | |
| 1 | PARTITION HASH ALL | | 1000M| 56G| | 3623K (3)| 12:04:40 | 1 | 4 |
|* 2 | HASH JOIN | | 1000M| 56G| 905M| 3623K (3)| 12:04:40 | | |
| 3 | TABLE ACCESS FULL| PS | 100M| 2479M| | 2726 (100)| 00:00:33 | 1 | 4 |
| 4 | TABLE ACCESS FULL| PN | 1000M| 32G| | 27047 (100)| 00:05:25 | 1 | 4 |
----------------------------------------------------------------------------------------------------
NOTE: It's clear that PS joined with PN in partition-wise join mode.
--Query 2
explain plan for select pn.u_name, ps.sales from pn, (select u_id, sum(u_sales) sales from ps group by u_id) ps where pn.u_id = ps.u_id;
@?/rdbms/admin/utlxplp
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000M| 63G| | 4392K (9)| 14:38:28 | | |
| 1 | PARTITION HASH ALL | | 1000M| 63G| | 4392K (9)| 14:38:28 | 1 | 4 |
| 2 | SORT GROUP BY | | 1000M| 63G| | 4392K (9)| 14:38:28 | | |
|* 3 | HASH JOIN | | 1000M| 63G| 905M| 4112K (2)| 13:42:28 | | |
| 4 | TABLE ACCESS FULL| PS | 100M| 2479M| | 2726 (100)| 00:00:33 | 1 | 4 |
| 5 | TABLE ACCESS FULL| PN | 1000M| 39G| | 27047 (100)| 00:05:25 | 1 | 4 |
-----------------------------------------------------------------------------------------------------
NOTE: PS joined with PN in partition-wise join mode also. My question is that why did "SORT GROUP BY" (OP#2) appear above "HASH JOIN" instead of "TABLE ACCESS FULL| PS"
--Query 3
explain plan for select pn.u_name, ps.u_sales from pn, (select * from ps union all select * from ps2) ps where pn.u_id = ps.u_id;
@?/rdbms/admin/utlxplp
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32G| 1817G| | 3623K (3)| 12:04:40 | | |
|* 1 | HASH JOIN | | 32G| 1817G| 3623M| 3623K (3)| 12:04:40 | | |
| 2 | VIEW | | 100M| 2479M| | 2726 (100)| 00:00:33 | | |
| 3 | UNION-ALL PARTITION| | | | | | | | |
| 4 | PARTITION HASH ALL| | 100M| 2479M| | 2726 (100)| 00:00:33 | 1 | 4 |
| 5 | TABLE ACCESS FULL| PS | 100M| 2479M| | 2726 (100)| 00:00:33 | 1 | 4 |
| 6 | PARTITION HASH ALL| | 2 | 52 | | 4 (0)| 00:00:01 | 1 | 4 |
| 7 | TABLE ACCESS FULL| PS2 | 2 | 52 | | 4 (0)| 00:00:01 | 1 | 4 |
| 8 | PARTITION HASH ALL | | 1000M| 32G| | 27047 (100)| 00:05:25 | 1 | 4 |
| 9 | TABLE ACCESS FULL | PN | 1000M| 32G| | 27047 (100)| 00:05:25 | 1 | 4 |
------------------------------------------------------------------------------------------------------
NOTE: PS didn't join with PN in partition-wise join mode. I though that the access path should be:
1, PS union all PS2 in partition level
2, the interim result set from Step1 joins PN in partition-wise join mode.
3, union all result from Step2.
Thanks for help!
Calvin
Last edited by Calvin_Qiu; 11-01-2005 at 04:47 AM.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|