(8.0.26)
drop table tab1;
create table tab1(
col1 bigint
);
drop table tab2;
create table tab2(
col1 bigint
);
drop table tab3;
create table tab3(
col1 bigint
);
drop table tab4;
create table tab4(
col1 bigint
);
drop procedure proc1;
delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
start transaction;
while i < x do
set i = i + 1;
insert into tab1 values(
1
);
insert into tab2 values(
1
);
insert into tab3 values(
1
);
insert into tab4 values(
1
);
end while;
commit;
end
//
delimiter ;
call proc1(100);
select count(*) from tab1;
select * from tab1 order by rand() limit 20;
select count(*) from tab2;
select * from tab2 order by rand() limit 20;
select count(*) from tab3;
select * from tab3 order by rand() limit 20;
select count(*) from tab4;
select * from tab4 order by rand() limit 20;
analyze table tab1;
analyze table tab2;
analyze table tab3;
analyze table tab4;
explain analyze
select /* NO_MERGE(t1) NO_MERGE(t2) */ count(*) from ( select t11.col1 col11,t12.col1 col12 from tab1 t11 inner join tab2 t12 on t11.col1 = t12.col1) t1
inner join ( select t21.col1 col21,t22.col1 col22 from tab3 t21 inner join tab4 t22 on t21.col1 = t22.col1) t2
on t1.col11 + t1.col12 = t2.col21 + t2.col22
;
show warnings;
10.45 sec
↓
↓ ビューマージ抑止
↓
4.31 sec
(19c)
drop table tab1 purge;
create table tab1(
col1 int
);
drop table tab2 purge;
create table tab2(
col1 int
);
drop table tab3 purge;
create table tab3(
col1 int
);
drop table tab4 purge;
create table tab4(
col1 int
);
declare
begin
for i in 1..100 loop
insert into tab1 values(
1
);
insert into tab2 values(
1
);
insert into tab3 values(
1
);
insert into tab4 values(
1
);
end loop;
end;
/
commit;
select count(*) from tab1;
select * from tab1 order by dbms_random.value() fetch first 20 rows only;
select count(*) from tab2;
select * from tab2 order by dbms_random.value() fetch first 20 rows only;
select count(*) from tab3;
select * from tab3 order by dbms_random.value() fetch first 20 rows only;
select count(*) from tab4;
select * from tab4 order by dbms_random.value() fetch first 20 rows only;
set time on
set timing on
exec dbms_stats.gather_table_stats(user,'TAB1');
exec dbms_stats.gather_table_stats(user,'TAB2');
exec dbms_stats.gather_table_stats(user,'TAB3');
exec dbms_stats.gather_table_stats(user,'TAB4');
explain plan for
select count(*) from ( select /* NO_MERGE */ t11.col1 col11,t12.col1 col12 from tab1 t11 inner join tab2 t12 on t11.col1 = t12.col1) t1
inner join ( select /* NO_MERGE */ t21.col1 col21,t22.col1 col22 from tab3 t21 inner join tab4 t22 on t21.col1 = t22.col1) t2
on t1.col11 + t1.col12 = t2.col21 + t2.col22
;
select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:07.74
↓
↓ ビューマージ抑止により直積解消
↓
経過: 00:00:02.18
(14)
drop table tab1;
create table tab1(
col1 bigint
);
drop table tab2;
create table tab2(
col1 bigint
);
drop table tab3;
create table tab3(
col1 bigint
);
drop table tab4;
create table tab4(
col1 bigint
);
start transaction;
insert into tab1 select
1
from generate_series(1,100) g;
commit;
start transaction;
insert into tab2 select
1
from generate_series(1,100) g;
commit;
start transaction;
insert into tab3 select
1
from generate_series(1,100) g;
commit;
start transaction;
insert into tab4 select
1
from generate_series(1,100) g;
commit;
select count(*) from tab1;
select * from tab1 order by random() limit 20;
select count(*) from tab2;
select * from tab2 order by random() limit 20;
select count(*) from tab3;
select * from tab3 order by random() limit 20;
select count(*) from tab4;
select * from tab4 order by random() limit 20;
\timing 1
\pset pager 0
set pg_hint_plan.debug_print = on;
analyze tab1;
analyze tab2;
analyze tab3;
analyze tab4;
/*+ Leading(( t22 (t12 (t11 t21) ) )) HashJoin(t11 t21) HashJoin(t11 t21 t12 ) HashJoin(t11 t21 t12 t22) */
explain analyze
select count(*) from ( select t11.col1 col11,t12.col1 col12 from tab1 t11 inner join tab2 t12 on t11.col1 = t12.col1) t1
inner join ( select t21.col1 col21,t22.col1 col22 from tab3 t21 inner join tab4 t22 on t21.col1 = t22.col1) t2
on t1.col11 + t1.col12 = t2.col21 + t2.col22
;
00:07.292
↓
↓ ビューマージ強制
↓
00:09.335
PostgreSQLの場合、ヒント句なしでもビューマージしない
(2019)
drop table tab1;
create table tab1(
col1 bigint not null
);
drop table tab2;
create table tab2(
col1 bigint not null
);
drop table tab3;
create table tab3(
col1 bigint not null
);
drop table tab4;
create table tab4(
col1 bigint not null
);
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 100
begin
insert into tab1 values(
1
);
insert into tab2 values(
1
);
insert into tab3 values(
1
);
insert into tab4 values(
1
);
set @i = @i + 1;
end
commit;
select count(*) from tab1;
select top 20 * from tab1 order by newid();
select count(*) from tab2;
select top 20 * from tab2 order by newid();
select count(*) from tab3;
select top 20 * from tab3 order by newid();
select count(*) from tab4;
select top 20 * from tab4 order by newid();
set statistics time on
update statistics tab1;
update statistics tab2;
update statistics tab3;
update statistics tab4;
select count(*) from ( select t11.col1 col11,t12.col1 col12 from tab1 t11 inner join tab2 t12 on t11.col1 = t12.col1) t1
inner join ( select t21.col1 col21,t22.col1 col22 from tab3 t21 inner join tab4 t22 on t21.col1 = t22.col1) t2
on t1.col11 + t1.col12 = t2.col21 + t2.col22
OPTION (MAXDOP 1)
;
→ビューマージしているが、Lazy Spoolにより早い模様。経過時間 = 140 ミリ秒
※hash joinやmerge joinのヒントを指定するとエラーとなる
Lazy Spool = 1 回にすべての行を処理するのではなく、スプールの親操作から行が要求されるたびに入力操作から行を取得し、その行をスプールに格納します。
select count(*) from ( select t11.col1 col11,t12.col1 col12 from tab1 t11 inner join tab2 t12 on t11.col1 = t12.col1) t1
inner join ( select t21.col1 col21,t22.col1 col22 from tab3 t21 inner join tab4 t22 on t21.col1 = t22.col1) t2
on t1.col11 + t1.col12 = t2.col21 + t2.col22
OPTION (MAXDOP 1, FORCE ORDER, HASH JOIN)
;
→ビューマージ抑止 経過時間 = 3587 ミリ秒
