パーティションワイズジョイン

MySQL

(8.0.18)

drop table tab1;
drop table tab2;

create table tab1(
col1 int
,col2 int
)
partition by range(col2)
( partition p0 values less than (10000)
,partition p1 values less than (20000)
,partition p2 values less than (30000)
,partition p3 values less than (40000)
,partition p4 values less than (50000)
,partition p5 values less than (60000)
,partition p6 values less than (70000)
,partition p7 values less than (80000)
,partition p8 values less than (90000)
,partition p9 values less than (maxvalue)
)
;

alter table tab1 add constraint tab1pk primary key (col1,col2);

create table tab2(
col1 int
,col2 int
)
partition by range(col2)
( partition p0 values less than (10000)
,partition p1 values less than (20000)
,partition p2 values less than (30000)
,partition p3 values less than (40000)
,partition p4 values less than (50000)
,partition p5 values less than (60000)
,partition p6 values less than (70000)
,partition p7 values less than (80000)
,partition p8 values less than (90000)
,partition p9 values less than (maxvalue)
)
;

alter table tab2 add constraint tab2pk primary key (col1,col2);

drop procedure proc1;

delimiter //
create procedure proc1(in param1 integer)
begin
declare i int;
truncate table tab1;
truncate table tab2;

set i = 1;
while i <= param1 do
insert into tab1 values(i,i);
insert into tab2 values(i,i);
set i = i + 1;
end while;

end
//
delimiter ;

call proc1(100000);

select count(*) from tab1;
select count(*) from tab1 partition(p0);
select count(*) from tab1 partition(p1);
select count(*) from tab1 partition(p2);
select count(*) from tab1 partition(p3);
select count(*) from tab1 partition(p4);
select count(*) from tab1 partition(p5);
select count(*) from tab1 partition(p6);
select count(*) from tab1 partition(p7);
select count(*) from tab1 partition(p8);
select count(*) from tab1 partition(p9);

select count(*) from tab2;
select count(*) from tab2 partition(p0);
select count(*) from tab2 partition(p1);
select count(*) from tab2 partition(p2);
select count(*) from tab2 partition(p3);
select count(*) from tab2 partition(p4);
select count(*) from tab2 partition(p5);
select count(*) from tab2 partition(p6);
select count(*) from tab2 partition(p7);
select count(*) from tab2 partition(p8);
select count(*) from tab2 partition(p9);

analyze table tab1;
analyze table tab2;

explain format=tree
select
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

→0.09 sec

--(2)非パーティションテーブルの場合

drop table tab1;
drop table tab2;

create table tab1(
col1 int
,col2 int
)
;

alter table tab1 add constraint tab1pk primary key (col1,col2);

create table tab2(
col1 int
,col2 int
)
;

alter table tab2 add constraint tab2pk primary key (col1,col2);

drop procedure proc1;

end
//
delimiter ;

call proc1(100000);

select count(*) from tab1;
select count(*) from tab2;

analyze table tab1;
analyze table tab2;

explain format=tree
select
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

→0.08 sec

パーティションワイズジョインはサポートされていない模様

Oracle

(19c)

[1]レンジパーティション

-- 1.1 パーティションテーブルの場合

drop table tab1 purge;
drop table tab2 purge;

create table tab1(
col1 number
,col2 number
)
partition by range(col2)
( partition p0 values less than (1)
,partition p1 values less than (2)
,partition p2 values less than (3)
,partition p3 values less than (4)
,partition p4 values less than (5)
,partition p5 values less than (6)
,partition p6 values less than (7)
,partition p7 values less than (8)
,partition p8 values less than (9)
,partition p9 values less than (maxvalue)
)
;

alter table tab1 add constraint tab1pk primary key(col1);

create table tab2(
col1 number
,col2 number
)
partition by range(col2)
( partition p0 values less than (1)
,partition p1 values less than (2)
,partition p2 values less than (3)
,partition p3 values less than (4)
,partition p4 values less than (5)
,partition p5 values less than (6)
,partition p6 values less than (7)
,partition p7 values less than (8)
,partition p8 values less than (9)
,partition p9 values less than (maxvalue)
)
;

alter table tab2 add constraint tab2pk primary key(col1);

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,trunc(dbms_random.value(0,1) * 10,3) );
insert into tab2 values(i,trunc(dbms_random.value(0,1) * 10,3) );
commit;
end loop;
end;
/

exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');

set time on
set timing on

explain plan for
select /*+ USE_HASH(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

→経過: 00:00:02.66

-- 1.2 非パーティションテーブルの場合

drop table tab1 purge;
drop table tab2 purge;

create table tab1(
col1 number
,col2 number
)
;

alter table tab1 add constraint tab1pk primary key(col1);

create table tab2(
col1 number
,col2 number
)
;

alter table tab2 add constraint tab2pk primary key(col1);

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,trunc(dbms_random.value(0,1) * 10,3) );
insert into tab2 values(i,trunc(dbms_random.value(0,1) * 10,3) );
commit;
end loop;
end;
/

select count(*) from tab1;

select count(*) from tab2;

exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');

set time on
set timing on

explain plan for
select /*+ USE_HASH(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

→経過: 00:00:08.45

パーティションワイズジョインは動作しており、
パーティションワイズジョインの効果はある模様

[2]ハッシュパーティション
-- 2.1 パーティションテーブルの場合 (パーティション数=32)

drop table tab1 purge;
drop table tab2 purge;

create table tab1(
col1 number
,col2 number
)
partition by hash(col2) partitions 32
;

create table tab2(
col1 number
,col2 number
)
partition by hash(col2) partitions 32
;

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,trunc(dbms_random.value(0,1) * 10,3) );
insert into tab2 values(i,trunc(dbms_random.value(0,1) * 10,3) );
commit;
end loop;
end;
/

exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');

set time on
set timing on

explain plan for
select /*+ USE_HASH(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

→経過: 00:00:02.40

-- 2.2 パーティションテーブルの場合 (パーティション数=8)

drop table tab1 purge;
drop table tab2 purge;

create table tab1(
col1 number
,col2 number
)
partition by hash(col2) partitions 8
;

create table tab2(
col1 number
,col2 number
)
partition by hash(col2) partitions 8
;

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,trunc(dbms_random.value(0,1) * 10,3) );
insert into tab2 values(i,trunc(dbms_random.value(0,1) * 10,3) );
commit;
end loop;
end;
/

exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');

set time on
set timing on

explain plan for
select /*+ USE_HASH(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

→経過: 00:00:02.80

-- 2.3 非パーティションテーブルの場合

drop table tab1 purge;
drop table tab2 purge;

create table tab1(
col1 number
,col2 number
)
;

create table tab2(
col1 number
,col2 number
)
;

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,trunc(dbms_random.value(0,1) * 10,3) );
insert into tab2 values(i,trunc(dbms_random.value(0,1) * 10,3) );
commit;
end loop;
end;
/

select count(*) from tab1;

select count(*) from tab2;

exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');

set time on
set timing on

explain plan for
select /*+ USE_HASH(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

→経過: 00:00:08.64

パーティションワイズジョインは動作しており、
パーティションワイズジョインの効果はある模様

PostgreSQL

(11)

--パーティションワイズジョインの有効化
show enable_partitionwise_join;
set enable_partitionwise_join = on;

--(1)パーティションテーブルの場合

drop table tab1;
drop table tab2;

create table tab1(
col1 numeric
,col2 numeric
)
partition by range(col2);

create table tab1p0 partition of tab1 FOR VALUES FROM (minvalue) TO (1);
create table tab1p1 partition of tab1 FOR VALUES FROM (1) TO (2);
create table tab1p2 partition of tab1 FOR VALUES FROM (2) TO (3);
create table tab1p3 partition of tab1 FOR VALUES FROM (3) TO (4);
create table tab1p4 partition of tab1 FOR VALUES FROM (4) TO (5);
create table tab1p5 partition of tab1 FOR VALUES FROM (5) TO (6);
create table tab1p6 partition of tab1 FOR VALUES FROM (6) TO (7);
create table tab1p7 partition of tab1 FOR VALUES FROM (7) TO (8);
create table tab1p8 partition of tab1 FOR VALUES FROM (8) TO (9);
create table tab1p9 partition of tab1 FOR VALUES FROM (9) TO (maxvalue);

alter table tab1p0 add constraint tab1p0pk primary key(col1);
alter table tab1p1 add constraint tab1p1pk primary key(col1);
alter table tab1p2 add constraint tab1p2pk primary key(col1);
alter table tab1p3 add constraint tab1p3pk primary key(col1);
alter table tab1p4 add constraint tab1p4pk primary key(col1);
alter table tab1p5 add constraint tab1p5pk primary key(col1);
alter table tab1p6 add constraint tab1p6pk primary key(col1);
alter table tab1p7 add constraint tab1p7pk primary key(col1);
alter table tab1p8 add constraint tab1p8pk primary key(col1);
alter table tab1p9 add constraint tab1p9pk primary key(col1);

create table tab2(
col1 numeric
,col2 numeric
)
partition by range(col2);

create table tab2p0 partition of tab2 FOR VALUES FROM (minvalue) TO (1);
create table tab2p1 partition of tab2 FOR VALUES FROM (1) TO (2);
create table tab2p2 partition of tab2 FOR VALUES FROM (2) TO (3);
create table tab2p3 partition of tab2 FOR VALUES FROM (3) TO (4);
create table tab2p4 partition of tab2 FOR VALUES FROM (4) TO (5);
create table tab2p5 partition of tab2 FOR VALUES FROM (5) TO (6);
create table tab2p6 partition of tab2 FOR VALUES FROM (6) TO (7);
create table tab2p7 partition of tab2 FOR VALUES FROM (7) TO (8);
create table tab2p8 partition of tab2 FOR VALUES FROM (8) TO (9);
create table tab2p9 partition of tab2 FOR VALUES FROM (9) TO (maxvalue);

alter table tab2p0 add constraint tab2p0pk primary key(col1);
alter table tab2p1 add constraint tab2p1pk primary key(col1);
alter table tab2p2 add constraint tab2p2pk primary key(col1);
alter table tab2p3 add constraint tab2p3pk primary key(col1);
alter table tab2p4 add constraint tab2p4pk primary key(col1);
alter table tab2p5 add constraint tab2p5pk primary key(col1);
alter table tab2p6 add constraint tab2p6pk primary key(col1);
alter table tab2p7 add constraint tab2p7pk primary key(col1);
alter table tab2p8 add constraint tab2p8pk primary key(col1);
alter table tab2p9 add constraint tab2p9pk primary key(col1);

insert into tab1 select generate_series(1,1000000) ,trunc( (random()*10)::numeric,3);
insert into tab2 select generate_series(1,1000000) ,trunc( (random()*10)::numeric,3);

select count(*) from tab1;
select count(*) from tab1p0;
select count(*) from tab1p1;
select count(*) from tab1p2;
select count(*) from tab1p3;
select count(*) from tab1p4;
select count(*) from tab1p5;
select count(*) from tab1p6;
select count(*) from tab1p7;
select count(*) from tab1p8;
select count(*) from tab1p9;

select count(*) from tab2;
select count(*) from tab2p0;
select count(*) from tab2p1;
select count(*) from tab2p2;
select count(*) from tab2p3;
select count(*) from tab2p4;
select count(*) from tab2p5;
select count(*) from tab2p6;
select count(*) from tab2p7;
select count(*) from tab2p8;
select count(*) from tab2p9;

analyze tab1;
analyze tab2;

\timing 1

explain analyze
select
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

→00:28.384
(※パーティションワイズジョイン無効化時→00:17.251)

--(2)非パーティションテーブルの場合

drop table tab1;
drop table tab2;

create table tab1(
col1 numeric
,col2 numeric
)
;

alter table tab1 add constraint tab1pk primary key(col1);

create table tab2(
col1 numeric
,col2 numeric
)
;

alter table tab2 add constraint tab2pk primary key(col1);

insert into tab1 select generate_series(1,1000000) ,trunc( (random()*10)::numeric,3);
insert into tab2 select generate_series(1,1000000) ,trunc( (random()*10)::numeric,3);

select count(*) from tab1;

select count(*) from tab2;

analyze tab1;
analyze tab2;

\timing 1

explain analyze
select
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

→00:24.473

パーティションワイズジョインは動作しているが、
パーティションワイズジョインの効果は確認できない

SQL Server

(2014)

--(1)パーティションテーブルの場合

use master
go

alter database test remove file f1;
alter database test remove filegroup fg1;

alter database test add filegroup fg1;

alter database test
add file
(
name = f1,
filename = 'c:\fg\f1.ndf',
size = 5mb
)
to filegroup fg1;

use test
go

drop partition scheme pscheme1;
drop partition function pfunc1;

create partition function pfunc1 (numeric(5,3) )
as range right for
values (1,2,3,4,5,6,7,8,9,10)
;

create partition scheme pscheme1
as partition pfunc1
to (fg1,fg1,fg1,fg1,fg1,fg1,fg1,fg1,fg1,fg1,fg1)
;

drop table tab1;
drop table tab2;

create table tab1(
col1 numeric(18,0) not null
,col2 numeric(5,3) not null
)
on pscheme1(col2)
;

alter table tab1 add constraint tab1pk primary key(col1,col2)
on pscheme1(col2)
;

create table tab2(
col1 numeric(18,0) not null
,col2 numeric(5,3) not null
)
on pscheme1(col2)
;

alter table tab2 add constraint tab2pk primary key(col1,col2)
on pscheme1(col2)
;

drop procedure proc1;

create procedure proc1(@param1 int)
as
begin
declare @i integer;
truncate table tab1;
truncate table tab2;

set @i = 1;
while @i <= @param1
begin
insert into tab1 values(@i,round(rand( convert(varbinary , newid() ) ) * 10,3,1) );
insert into tab2 values(@i,round(rand( convert(varbinary , newid() ) ) * 10,3,1) );
set @i = @i + 1;
end
end
;

exec proc1 1000000;

select count(*) from tab1;

select $partition.pfunc1(col2),count(*)
from tab1
group by $partition.pfunc1(col2)
order by $partition.pfunc1(col2)
;

select count(*) from tab2;

select $partition.pfunc1(col2),count(*)
from tab2
group by $partition.pfunc1(col2)
order by $partition.pfunc1(col2)
;

update statistics tab1;
update statistics tab2;

set statistics time on
go

select
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

→経過時間 = 778 ミリ秒

--(2)非パーティションテーブルの場合

drop table tab1;
drop table tab2;

create table tab1(
col1 numeric(18,0) not null
,col2 numeric(5,3) not null
)
;

alter table tab1 add constraint tab1pk primary key(col1,col2)
;

create table tab2(
col1 numeric(18,0) not null
,col2 numeric(5,3) not null
)
;

alter table tab2 add constraint tab2pk primary key(col1,col2)
;

drop procedure proc1;

exec proc1 1000000;

select count(*) from tab1;
select count(*) from tab2;

update statistics tab1;
update statistics tab2;

set statistics time on
go

select
count(*)
from tab1 A inner join tab2 B
on A.col2 = B.col2
;

→経過時間 = 746 ミリ秒

パーティションワイズジョインはサポートされていない模様