| partition-wise join in different cases [message #145127] |
Tue, 01 November 2005 00:39  |
Calvin_Qiu
Messages: 1
Registered: November 2005
|
Junior Member |
|
|
Hi, all
Following is an example of partition-wise join in different cases, and leas 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');
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
|
|
|
|
| Re: partition-wise join in different cases [message #145247 is a reply to message #145127] |
Wed, 02 November 2005 00:19  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
In query 2, the inline view is mergable. Oracle has found a better way to run the query by rewriting it with the join inside the view.
Edited (RL): removed incorrect stuff about query_rewrite_enabled. See next post in this thread.
In query 3, the UNION ALL makes the view non-mergeable. It has to fully resolve the UNION ALL before it can join to PS.
The UNION ALL PARTITION is not telling you what you think: the PARTITION does not refer to partitioned tables, but to partitioned views. Partitioned views (SELECT * from a UNION ALL select * from b ...) were deprecated in v8i, but are still supported. Grab a copy of the v7.3 Concepts manual and look up Partitioned Views for more detail.
Having said that though, I'm surprised that the query is not re-writable - Partition Views still rate a mention in the 10g manuals because they work with STAR_TRANSFORMATION query rewrites (although nowhere in the 10g manuals do they tell you what a Patition View avtually IS  )
_____________
Ross Leishman
[Updated on: Wed, 02 November 2005 15:57] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
