DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Partition-Wise Join in different cases

Threaded View

  1. #1
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327

    Exclamation 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
  •  


Click Here to Expand Forum to Full Width