(8.0.26)
drop table tab1;
create table tab1(
col1 bigint
,col2 bigint
);
drop table tab2;
create table tab2(
col1 bigint
,col2 bigint
);
drop table tab3;
create table tab3(
col1 bigint
,col2 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(
i
,1
);
end while;
commit;
end
//
delimiter ;
call proc1(100);
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 tab2 values(
i
,1
);
insert into tab3 values(
i
,1
);
end while;
commit;
end
//
delimiter ;
call proc1(3000);
analyze table tab1;
analyze table tab2;
analyze table tab3;
explain format=tree
delete from tab1 t1
where exists ( select 1 from (
select t2.col1 from tab2 t2
inner join tab3 t3 on t2.col2 = t3.col2
) t0 where t1.col1 = t0.col1
);
show warnings;
explain format=tree
delete from tab1 t1
where exists ( select 1 from (
select t2.col1 from tab2 t2
inner join tab3 t3 on t2.col2 = t3.col2
inner join tab1 t5 on t2.col1 = t5.col1
) t0 where t1.col1 = t0.col1
);
show warnings;
start transaction;
rollback;
2.14 sec
↓
0.24 sec
(19c)
drop table tab1 purge;
create table tab1(
col1 int
,col2 int
);
drop table tab2 purge;
create table tab2(
col1 int
,col2 int
);
drop table tab3 purge;
create table tab3(
col1 int
,col2 int
);
drop table tab4 purge;
create table tab4(
col1 int
,col2 int
);
declare
begin
for i in 1..100 loop
insert into tab1 values(
i
,1
);
end loop;
end;
/
declare
begin
for i in 1..1000000 loop
insert into tab2 values(
i
,1
);
insert into tab3 values(
i
,1
);
insert into tab4 values(
i
,1
);
end loop;
end;
/
commit;
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
delete from tab1 t1
where exists ( select 1 from (
select t2.col1 from tab2 t2
inner join tab3 t3 on t2.col2 = t3.col2
inner join tab4 t4 on t3.col2 = t4.col2
) t0 where t1.col1 = t0.col1
);
select * from table(dbms_xplan.display(format=>'ALL') );
explain plan for
delete from tab1 t1
where exists ( select 1 from (
select t2.col1 from tab2 t2
inner join tab3 t3 on t2.col2 = t3.col2
inner join tab4 t4 on t3.col2 = t4.col2
inner join tab1 t5 on t2.col1 = t5.col1
) t0 where t1.col1 = t0.col1
);
select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:00.50
↓
経過: 00:00:00.12
(14)
drop table tab1;
create table tab1(
col1 bigint
,col2 bigint
);
drop table tab2;
create table tab2(
col1 bigint
,col2 bigint
);
drop table tab3;
create table tab3(
col1 bigint
,col2 bigint
);
start transaction;
insert into tab1 select
g
,1
from generate_series(1,100) g;
commit;
start transaction;
insert into tab2 select
g
,1
from generate_series(1,6000) g;
commit;
start transaction;
insert into tab3 select
g
,1
from generate_series(1,6000) g;
commit;
\timing 1
\pset pager 0
analyze tab1;
analyze tab2;
analyze tab3;
explain
delete from tab1 t1
where exists ( select 1 from (
select t2.col1 from tab2 t2
inner join tab3 t3 on t2.col2 = t3.col2
) t0 where t1.col1 = t0.col1
);
explain
delete from tab1 t1
where exists ( select 1 from (
select t2.col1 from tab2 t2
inner join tab3 t3 on t2.col2 = t3.col2
inner join tab1 t5 on t2.col1 = t5.col1
) t0 where t1.col1 = t0.col1
);
start transaction;
rollback;
2176.051 ミリ秒
↓
94.718 ミリ秒
(2019)
drop table tab1;
create table tab1(
col1 bigint not null
,col2 bigint
);
drop table tab2;
create table tab2(
col1 bigint not null
,col2 bigint
);
drop table tab3;
create table tab3(
col1 bigint not null
,col2 bigint
);
drop table tab4;
create table tab4(
col1 bigint not null
,col2 bigint
);
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 100
begin
insert into tab1 values(
@i
,1
);
set @i = @i + 1;
end
commit;
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
insert into tab2 values(
@i
,1
);
insert into tab3 values(
@i
,1
);
insert into tab4 values(
@i
,1
);
set @i = @i + 1;
end
commit;
update statistics tab1;
update statistics tab2;
update statistics tab3;
update statistics tab4;
set statistics time on
delete from tab1
where exists ( select 1 from (
select t2.col1 from tab2 t2
inner join tab3 t3 on t2.col2 = t3.col2
inner join tab4 t4 on t3.col2 = t4.col2
) t0 where col1 = t0.col1
);
delete from tab1
where exists ( select 1 from (
select t2.col1 from tab2 t2
inner join tab3 t3 on t2.col2 = t3.col2
inner join tab4 t4 on t3.col2 = t4.col2
inner join tab1 t5 on t2.col1 = t5.col1
) t0 where col1 = t0.col1
);
begin transaction;
rollback;
経過時間 = 0 ミリ秒
↓
経過時間 = 1 ミリ秒
SQL ServerはLazy Spoolによりもともと早い。
※hash joinやmerge joinのヒントを指定するとエラーとなる
Lazy Spool = 1 回にすべての行を処理するのではなく、スプールの親操作から行が要求されるたびに入力操作から行を取得し、その行をスプールに格納します。
