hashパーティション
パーティション数=10
1000万件のテーブルを作成
個別キー数 = 100件でグループ化
(8.0.29)
drop table tab1;
create table tab1(
col1 int
,col2 int
)
partition by hash(col1)
(partition p00,
partition p01,
partition p02,
partition p03,
partition p04,
partition p05,
partition p06,
partition p07,
partition p08,
partition p09)
;
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
start transaction;
while i <= 10000000 do
insert into tab1 select floor(rand() * 10000000)+1,mod(floor(rand() * 10000000)+1,100);
set i = i + 1;
end while;
commit;
end
//
delimiter ;
call proc1();
select count(*) from tab1;
select count(*) from tab1 partition(p00);
select count(*) from tab1 partition(p01);
select count(*) from tab1 partition(p02);
select count(*) from tab1 partition(p03);
select count(*) from tab1 partition(p04);
select count(*) from tab1 partition(p05);
select count(*) from tab1 partition(p06);
select count(*) from tab1 partition(p07);
select count(*) from tab1 partition(p08);
select count(*) from tab1 partition(p09);
analyze table tab1;
explain format=tree
select col2,count(*) from tab1
group by col2
order by col2
;
explain format=tree
select A.col2,sum(A.cnt) from
(
select col2,count(*) cnt from tab1 partition(p00) group by col2
union all
select col2,count(*) cnt from tab1 partition(p01) group by col2
union all
select col2,count(*) cnt from tab1 partition(p02) group by col2
union all
select col2,count(*) cnt from tab1 partition(p03) group by col2
union all
select col2,count(*) cnt from tab1 partition(p04) group by col2
union all
select col2,count(*) cnt from tab1 partition(p05) group by col2
union all
select col2,count(*) cnt from tab1 partition(p06) group by col2
union all
select col2,count(*) cnt from tab1 partition(p07) group by col2
union all
select col2,count(*) cnt from tab1 partition(p08) group by col2
union all
select col2,count(*) cnt from tab1 partition(p09) group by col2
) A
group by A.col2
order by A.col2
;
-- 結果
パーティションテーブルの場合
5.97 sec
パーティションテーブルで、SQLレベルでパーティション毎に集約した場合
6.06 sec
非パーティションテーブルの場合
5.92 sec
(19c)
drop table tab1 purge;
create table tab1(
col1 int
,col2 int
)
partition by hash(col1)
( partition p00
, partition p01
, partition p02
, partition p03
, partition p04
, partition p05
, partition p06
, partition p07
, partition p08
, partition p09
);
declare
begin
for i in 1..10000000 loop
insert into tab1 select floor(dbms_random.value(1, 10000001) ), mod( floor(dbms_random.value(1, 10000001) ),100) from dual;
end loop;
end;
/
commit;
set time on
set timing on
select count(*) from tab1;
select count(*) from tab1 partition(p00);
select count(*) from tab1 partition(p01);
select count(*) from tab1 partition(p02);
select count(*) from tab1 partition(p03);
select count(*) from tab1 partition(p04);
select count(*) from tab1 partition(p05);
select count(*) from tab1 partition(p06);
select count(*) from tab1 partition(p07);
select count(*) from tab1 partition(p08);
select count(*) from tab1 partition(p09);
exec dbms_stats.gather_table_stats(user, 'TAB1');
explain plan for
select col2,count(*) from tab1
group by col2
order by col2
;
select * from table(dbms_xplan.display(format=>'ALL') );
explain plan for
select A.col2,sum(A.cnt) from
(
select col2,count(*) cnt from tab1 partition(p00) group by col2
union all
select col2,count(*) cnt from tab1 partition(p01) group by col2
union all
select col2,count(*) cnt from tab1 partition(p02) group by col2
union all
select col2,count(*) cnt from tab1 partition(p03) group by col2
union all
select col2,count(*) cnt from tab1 partition(p04) group by col2
union all
select col2,count(*) cnt from tab1 partition(p05) group by col2
union all
select col2,count(*) cnt from tab1 partition(p06) group by col2
union all
select col2,count(*) cnt from tab1 partition(p07) group by col2
union all
select col2,count(*) cnt from tab1 partition(p08) group by col2
union all
select col2,count(*) cnt from tab1 partition(p09) group by col2
) A
group by A.col2
order by A.col2
;
-- 結果
パーティションテーブルの場合
経過: 00:00:01.50
パーティションテーブルで、SQLレベルでパーティション毎に集約した場合
経過: 00:00:00.51
非パーティションテーブルの場合
経過: 00:00:01.55
(14)
https://tech-lab.sios.jp/archives/11829
create table tab1(
col1 int
,col2 int
)
partition by hash(col1);
create table tab1p00 partition of tab1 for values with (modulus 10,remainder 0);
create table tab1p01 partition of tab1 for values with (modulus 10,remainder 1);
create table tab1p02 partition of tab1 for values with (modulus 10,remainder 2);
create table tab1p03 partition of tab1 for values with (modulus 10,remainder 3);
create table tab1p04 partition of tab1 for values with (modulus 10,remainder 4);
create table tab1p05 partition of tab1 for values with (modulus 10,remainder 5);
create table tab1p06 partition of tab1 for values with (modulus 10,remainder 6);
create table tab1p07 partition of tab1 for values with (modulus 10,remainder 7);
create table tab1p08 partition of tab1 for values with (modulus 10,remainder 8);
create table tab1p09 partition of tab1 for values with (modulus 10,remainder 9);
\pset pager 0
\d+ tab1
insert into tab1
select floor(random() * 10000000)+1
, mod( (floor(random() * 10000000)+1)::int,100)
from generate_series(1,10000000)
;
select count(*) from tab1;
select count(*) from tab1p00;
analyze tab1;
explain analyze
select col2,count(*) from tab1
group by col2
order by col2
;
set enable_partitionwise_aggregate = on;
set enable_partitionwise_aggregate = off;
show enable_partitionwise_aggregate;
explain analyze
select A.col2,sum(A.cnt) from
(
select col2,count(*) cnt from tab1p00 group by col2
union all
select col2,count(*) cnt from tab1p01 group by col2
union all
select col2,count(*) cnt from tab1p02 group by col2
union all
select col2,count(*) cnt from tab1p03 group by col2
union all
select col2,count(*) cnt from tab1p04 group by col2
union all
select col2,count(*) cnt from tab1p05 group by col2
union all
select col2,count(*) cnt from tab1p06 group by col2
union all
select col2,count(*) cnt from tab1p07 group by col2
union all
select col2,count(*) cnt from tab1p08 group by col2
union all
select col2,count(*) cnt from tab1p09 group by col2
) A
group by A.col2
order by A.col2
;
-- 結果
パーティションテーブルで、enable_partitionwise_aggregate がoffの場合
Planning Time: 0.112 ms
Execution Time: 2743.284 ms
パーティションテーブルで、enable_partitionwise_aggregate がonの場合
Planning Time: 0.174 ms
Execution Time: 2137.928 ms
パーティションテーブルで、SQLレベルでパーティション毎に集約した場合
Planning Time: 0.213 ms
Execution Time: 2056.535 ms
非パーティションテーブルの場合
Planning Time: 0.051 ms
Execution Time: 2075.723 ms
パーティションワイズ集約の効果はすこしある。
また、SQL書き換えでパーティション毎に集約した場合とenable_partitionwise_aggregateをonにした場合は同等
非パーティションテーブルはパーティションワイズ集約後と同等の処理時間
(2019)
特定パーティションのみのデータ取得方法がない模様のため、検証不可
