パーティションプルーニング確認

MySQL

(5.6)

drop table tab1;

create table tab1(
col1 int
,col2 int
,col3 int
)
partition by list(col2)
( partition tab1p0 values in (0),
partition tab1p1 values in (1)
);

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

※主キーにはパーティションキーを含める必要がある

drop procedure proc1;

delimiter //
create procedure proc1()
begin
declare i int;

set i = 1;

while i <= 100000 do
insert into tab1 values(i,mod(floor(rand()*10),2),mod(floor(rand()*10),2));
set i = i + 1;
end while;
end
//
delimiter ;

call proc1();

select count(*) from tab1;
select count(*) from tab1 partition(tab1p0);
select count(*) from tab1 partition(tab1p1);

analyze table tab1;

explain
select * from tab1
where col2 = 1
;

explain
select * from tab1
where col3 = 1
;

実行計画確認結果:
rowsの件数が相違。その他相違なし。

select count(*) from (
select * from tab1
where col2 = 1
) A;

→経過: 0.02 sec

--非パーティションとの比較
drop table tab1;

create table tab1(
col1 int
,col2 int
,col3 int
);

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

drop procedure proc1;

call proc1();

select count(*) from tab1;
analyze table tab1;

select count(*) from (
select * from tab1
where mod(col2,2) = 1
) A;

→0.04 sec
→プルーニングの効果ある模様

Oracle

(12cR1)

drop table tab1 purge;

create table tab1(
col1 int
,col2 int
,col3 int
)
partition by list(col2)
( partition tab1p0 values (0),
partition tab1p1 values (1)
);

create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1p primary key(col1) using index ind1;

declare
begin

for i in 1..1000000 loop

insert into tab1 values(i,mod(floor(dbms_random.value(0, 10)),2),mod(floor(dbms_random.value(0, 10)),2));
commit;
end loop;
end;
/

select count(*) from tab1;
select count(*) from tab1 partition(tab1p0);
select count(*) from tab1 partition(tab1p1);

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

explain plan for
select * from tab1
where col2 = 1
;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain plan for
select * from tab1
where col3 = 1
;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

実行計画確認結果:
where句にパーティションキーを含む場合、対象パーティションのみアクセス
where句にパーティションキーを含まない場合、全パーティションをアクセス

select count(*) from (
select * from tab1
where col2 = 1
);
→経過: 00:00:00.01

--非パーティションとの比較
drop table tab1 purge;

create table tab1(
col1 int
,col2 int
,col3 int
)
;
create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1p primary key(col1) using index ind1;

declare
begin

for i in 1..1000000 loop

insert into tab1 values(i,mod(floor(dbms_random.value(0, 10)),2),mod(floor(dbms_random.value(0, 10)),2));
commit;
end loop;
end;
/

select count(*) from tab1;

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

select count(*) from (
select * from tab1
where col2 = 1
);

→経過: 00:00:00.02
→プルーニングの効果ある模様

PostgreSQL

(10)
(11)

drop table tab1;

create table tab1(
col1 int
,col2 int
,col3 int
)
partition by list(col2);

create table tab1p0 partition of tab1 for values in (0);
alter table tab1p0 add constraint tab1p0pk primary key(col1);

create table tab1p1 partition of tab1 for values in (1);
alter table tab1p1 add constraint tab1p1pk primary key(col1);

insert into tab1 select generate_series(1,100000),mod((random()*10)::int,2),mod((random()*10)::int,2);

select count(*) from tab1;
select count(*) from tab1p0;
select count(*) from tab1p1;

analyze tab1;

explain analyze
select * from tab1
where col2 = 1
;

explain analyze
select * from tab1
where col3 = 1
;

Execution Time→ 約 8ms

--非パーティションとの比較
drop table tab1;

create table tab1(
col1 int
,col2 int
,col3 int
)
;

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

insert into tab1 select generate_series(1,100000),mod((random()*10)::int,2),mod((random()*10)::int,2);

select count(*) from tab1;

analyze tab1;

explain analyze
select * from tab1
where col2 = 1
;

Execution Time→ 約 9ms
→プルーニングの効果ある模様

SQL Server

(2014)

use master
go
alter database test add filegroup fg1;
alter database test add filegroup fg2;
alter database test add filegroup fg3;

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

use test
go

drop table tab1;
drop partition scheme pscheme1;
drop partition function pfunc1;

create partition function pfunc1 (int)
as range right for
values (0,1)
;

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

create table tab1(
col1 int not null
,col2 int not null
,col3 int not null
)
on pscheme1(col2)
;

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

with t1(col1) as(
select 1
union all
select col1+1
from t1
where col1+1 <= 100000
)
insert into tab1
select
col1
,0
, cast(floor(rand(convert(varbinary , newid())) * 10) as integer) % 2
from t1
OPTION ( MAXRECURSION 0 )
;

※SQL Serverの場合、行ごとに乱数をセットするためにnewidをシードとして付与する必要あり
※なぜか末尾のカラムのみ乱数値が設定される

update tab1 set col2 = col3;

select count(*) from tab1;

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

update statistics tab1;

set showplan_text on
go

select * from tab1
where col2 = 1
;

select * from tab1
where col3 = 1
;
実行計画確認結果:

where句にパーティションキーを含む場合、対象パーティションのみアクセス
where句にパーティションキーを含まない場合、全パーティションをアクセス

set statistics time on
go

経過時間 = 174 ミリ秒

--非パーティションとの比較

drop table tab1;

create table tab1(
col1 int not null
,col2 int not null
,col3 int not null
)
;

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

update tab1 set col2 = col3;

select count(*) from tab1;

update statistics tab1;

set statistics time on
go

select * from tab1
where col2 = 1
;

経過時間 = 188 ミリ秒
→プルーニングの効果ある模様