Posts Tagged ‘Partition Outer Join’
The performance of partition outer join is better than Cartesian product join, in my case.
In my previous post, I was mentioned about partition out join. This time I took performance test of partition outer join. Maybe, the result of the performance test depends on the test environment. But my test result shows that partition outer join is better than Cartesian product join.
(1) Create a table and test data loading.
CREATE TABLE t2( site CHAR( 100 ) , item CHAR( 100 ) , category CHAR( 100 ) , amount NUMBER ) ;
--#of Branch : 10000
--#of Item : 5
--#of Category : 10
INSERT
INTO t2
SELECT 'Site-' ||lpad( site , 5 , '0' ) site ,
'Item-' ||lpad( decode( item , 0 , 5 , item ) , 2 , '0' ) item ,
'Category-' ||lpad( decode( category , 0 , 10 , category ) , 2 , '0' ) category ,
amount
FROM (
SELECT NTILE( 10000 ) over (ORDER BY LEVEL) site ,
MOD( NTILE( 50000 ) over (ORDER BY LEVEL) , 5 ) item ,
MOD( LEVEL , 10 ) category ,
LEVEL amount
FROM dual
CONNECT BY LEVEL <=500000
) a ;
commit;
-- delete 5 rows
delete from t2 where site='Site-00010' and item='Item-04' and category between 'Category-03' and 'Category-07';
commit;
(2) The result of Cartesian join method.
alter system flush buffer_cache;
WITH v1 AS (
SELECT a.site ,
a.item ,
a.category ,
SUM( a.amount ) amount
FROM t2 a
GROUP BY a.site ,
a.item ,
a.category
)
SELECT v2.site ,
v2.item ,
v2.category ,
NVL( v1.amount , 0 ) amount
FROM v1 ,
(
SELECT a.site ,
a.item ,
copy.category
FROM v1 a ,
(
SELECT
CASE LEVEL
WHEN 1 THEN 'C01'
WHEN 2 THEN 'C02'
WHEN 3 THEN 'C03'
WHEN 4 THEN 'C04'
WHEN 5 THEN 'C05'
WHEN 6 THEN 'C06'
WHEN 7 THEN 'C07'
WHEN 8 THEN 'C08'
WHEN 9 THEN 'C09'
WHEN 10 THEN 'C10'
END category
FROM dual
CONNECT BY level<=10
) copy
GROUP BY a.site ,
a.item ,
copy.category
) v2
WHERE v1.site( + ) = v2.site
AND v1.item( + ) = v2.item
AND v1.category( + ) = v2.category
ORDER BY 1 , 2 , 3
call count cpu elapsed disk query current rows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.56 3.91 489 660 2 0 1 0.00
Exec 1 30.59 37.13 39885 22029 22085 0 0 14.51
Fetch 20001 228.03 454.96 328712 43490 12 500000 0 328.10
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 20003 259.18 496.00 369086 66179 22099 500000 1 342.61
Event waited on Count Zero Ela Elapse AVG(Ela) MAX(Ela) Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
enq: RO - fast object reuse 2 0 0.04 0.02 0.02 0
Disk file operations I/O 2 0 0.00 0.00 0.00 0
db file sequential read 4 0 0.03 0.01 0.02 4
SQL*Net message to client 20002 0 0.09 0.00 0.00 0
direct path write temp 3025 0 47.95 0.02 0.30 0
log file sync 1 0 0.01 0.01 0.01 0
direct path read 65 0 1.44 0.02 0.11 0
SQL*Net message from client 20002 0 41.10 0.00 4.64 0
reliable message 2 0 0.01 0.00 0.01 0
direct path sync 1 0 0.05 0.05 0.05 0
direct path read temp 24749 0 256.52 0.01 0.34 0
Rows Row Source Operation
---------- ---------------------------------------------------
500000 TEMP TABLE TRANSFORMATION (cr=65519 pr=368597 pw=209624 time=485942990 us)
0 LOAD AS SELECT (cr=22029 pr=39885 pw=39781 time=37084935 us)
499995 HASH GROUP BY (cr=22029 pr=39883 pw=18042 time=26659340 us cost=6166 size=226319654 card=709466)
499995 TABLE ACCESS FULL T2 (cr=22029 pr=21841 pw=0 time=2615698 us cost=6089 size=226319654 card=709466)
500000 SORT ORDER BY (cr=43490 pr=328712 pw=169843 time=447605455 us cost=40396 size=111312432 card=210819)
500000 HASH JOIN OUTER (cr=43490 pr=315195 pw=156326 time=421193398 us cost=16810 size=111312432 card=210819)
500000 VIEW (cr=21746 pr=276820 pw=139691 time=263743564 us cost=8440 size=1045 card=5)
500000 HASH GROUP BY (cr=21746 pr=276820 pw=139691 time=262628114 us cost=8440 size=1045 card=5)
4999950 MERGE JOIN CARTESIAN (cr=21746 pr=149950 pw=12821 time=197411590 us cost=8363 size=148278394 card=709466)
10 VIEW (cr=0 pr=0 pw=0 time=952 us cost=2 size=5 card=1)
10 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=719 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=16 us cost=2 size=0 card=1)
4999950 BUFFER SORT (cr=21746 pr=149950 pw=12821 time=186241554 us cost=8440 size=144731064 card=709466)
499995 VIEW (cr=21746 pr=21740 pw=0 time=3686685 us cost=8361 size=144731064 card=709466)
499995 TABLE ACCESS FULL SYS_TEMP_0FD9D6C8A_283648BD (cr=21746 pr=21740 pw=0 time=2634086 us cost=8361 size=226319654 card=709466)
499995 VIEW (cr=21744 pr=21740 pw=0 time=4269514 us cost=8361 size=226319654 card=709466)
499995 TABLE ACCESS FULL SYS_TEMP_0FD9D6C8A_283648BD (cr=21744 pr=21740 pw=0 time=3210517 us cost=8361 size=226319654 card=709466)
(3) The result of partition outer join method.
alter system flush buffer_cache;
WITH v1 AS (
SELECT a.site ,
a.item ,
a.category ,
SUM( a.amount ) amount
FROM t2 a
GROUP BY a.site ,
a.item ,
a.category
)
SELECT a.site ,
a.item ,
copy.category ,
NVL( a.amount , 0 )
FROM (
SELECT
CASE LEVEL
WHEN 1 THEN 'C01'
WHEN 2 THEN 'C02'
WHEN 3 THEN 'C03'
WHEN 4 THEN 'C04'
WHEN 5 THEN 'C05'
WHEN 6 THEN 'C06'
WHEN 7 THEN 'C07'
WHEN 8 THEN 'C08'
WHEN 9 THEN 'C09'
WHEN 10 THEN 'C10'
END category
FROM dual
CONNECT BY level <=10
) copy left outer join v1 a PARTITION BY ( a.site , a.item ) ON copy.category = a.category
ORDER BY 1 , 2, 3
call count cpu elapsed disk query current rows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.34 2.51 451 277 1 0 1 0.00
Exec 1 0.00 0.00 0 0 0 0 0 0.00
Fetch 20001 109.33 142.74 94971 22029 23 500000 0 91.02
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 20003 109.67 145.25 95422 22306 24 500000 1 91.02
Event waited on Count Zero Ela Elapse AVG(Ela) MAX(Ela) Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
Disk file operations I/O 1 0 0.00 0.00 0.00 0
direct path write temp 1703 0 42.50 0.02 0.25 0
SQL*Net message to client 20002 0 0.10 0.00 0.00 0
direct path read 50 0 1.01 0.02 0.12 0
SQL*Net message from client 20002 0 43.22 0.00 4.98 0
direct path read temp 3517 0 9.17 0.00 0.32 0
Rows Row Source Operation
---------- ---------------------------------------------------
500000 SORT ORDER BY (cr=22029 pr=94971 pw=73130 time=192705062 us cost=55040 size=1575090 card=7095)
500000 VIEW (cr=22029 pr=81457 pw=59616 time=120167261 us cost=54694 size=1575090 card=7095)
500000 NESTED LOOPS PARTITION OUTER (cr=22029 pr=81457 pw=59616 time=118555905 us cost=54694 size=2298780 card=7095)
500001 BUFFER SORT (cr=0 pr=0 pw=0 time=1818543 us)
10 VIEW (cr=0 pr=0 pw=0 time=487 us cost=2 size=5 card=1)
10 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=405 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=10 us cost=2 size=0 card=1)
0 FILTER (cr=22029 pr=81457 pw=59616 time=107572004 us)
4999950 SORT PARTITION JOIN (cr=22029 pr=81457 pw=59616 time=100176688 us cost=54681 size=2263305 card=7095)
499995 VIEW (cr=22029 pr=61849 pw=40008 time=52874935 us cost=6166 size=2263305 card=7095)
499995 SORT GROUP BY (cr=22029 pr=61849 pw=40008 time=51216115 us cost=6166 size=226319654 card=709466)
499995 TABLE ACCESS FULL T2 (cr=22029 pr=21841 pw=0 time=2415314 us cost=6089 size=226319654 card=709466)
The simple example of partition outer join.
Partition outer join was introuced at Oracle 10g. The feature of partition outer join is very useful, especially, when you make a UI query. Suppose, you are a sales manager and you should anlayze the sales report everyday. So, you want a user-friendly report template. For example, the layout of second report is more user-friendly than first report.
The layout of first report.
The layout of second report.
To make the layout of scecondary report, we usually use catesian product join. like this.
create table t1 (
site char(10),
item char(10),
category char(10),
amount number);
insert into t1
select 'SITE'||case when level<=8 then '01' else '02' end,
'ITEM'||case when level<=8 then '01' else '02' end,
'C'||lpad(to_char(mod(level,8)+1),2,'0'),
level*100
from dual connect by level<=16;
commit;
with v1 as(
select a.site, a.item, a.category, sum(a.amount) amount
from t1 a
group by a.site, a.item, a.category
)
select v2.site, v2.item, v2.category, nvl(v1.amount,0) amount
from v1, ( select a.site, a.item, copy.category
from v1 a, (select case level when 1 then 'C01'
when 2 then 'C02'
when 3 then 'C03'
when 4 then 'C04'
when 5 then 'C05'
when 6 then 'C06'
when 7 then 'C07'
when 8 then 'C08'
when 9 then 'C09'
when 10 then 'C10' end category
from dual connect by level<=10) copy
group by a.site, a.item, copy.category) v2
where v1.site(+) = v2.site
and v1.item(+) = v2.item
and v1.category(+) = v2.category
order by 1,2,3;
But, from oracle 10g, we can use partition outer join method, like this.
with v1 as(
select a.site, a.item, a.category, sum(a.amount) amount
from t1 a
group by a.site, a.item, a.category
)
select a.site, a.item, copy.category, nvl(a.amount,0)
from (select case level when 1 then 'C01'
when 2 then 'C02'
when 3 then 'C03'
when 4 then 'C04'
when 5 then 'C05'
when 6 then 'C06'
when 7 then 'C07'
when 8 then 'C08'
when 9 then 'C09'
when 10 then 'C10' end category
from dual connect by level<=10) copy
left outer join v1 a
PARTITION BY (a.site, a.item)
on copy.category = a.category
order by 1,2;
DBMS Tuning & My Life 