まとめ
データに依存すると思われるが、ORをUNION-ALLに変換することで速くなった。
Oracleは何もしなくてもUNION-ALLに変換された。
(8.0.26)
drop table tab1;
create table tab1(
col1 bigint
,col2 bigint
,col3 bigint
,col4 bigint
);
drop table tab2;
create table tab2(
col1 bigint
,col2 bigint
,col3 bigint
,col4 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
,floor(rand() * 1000)+1
,floor(rand() * 1000)+1
,floor(rand() * 1000000)+1
);
insert into tab2 values(
i
,floor(rand() * 10000)+1
,floor(rand() * 10000)+1
,floor(rand() * 1000000)+1
);
end while;
commit;
end
//
delimiter ;
call proc1(1000000);
select count(*) from tab1;
select * from tab1 order by rand() limit 20;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
select count(*) from tab2;
select * from tab2 order by rand() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);
create index ind23 on tab2(col3);
create index ind24 on tab2(col4);
analyze table tab1;
analyze table tab2;
explain
select count(distinct t1.col1)
from tab1 t1
inner join tab2 t2
on ( t1.col2 = t2.col2 or t1.col3 = t2.col3 )
where t1.col4 < 1000
;
→(0.32 sec)
実行計画: Range checked for each record (index map: 0x6)
explain
select count(distinct t1.col1)
from (
select t11.col1
from tab1 t11
inner join tab2 t12
on ( t11.col2 = t12.col2 )
where t11.col4 < 1000
UNION ALL
select t21.col1
from tab1 t21
inner join tab2 t22
on ( t21.col3 = t22.col3 )
where t21.col4 < 1000
) t1;
→(0.07 sec)★
実行計画: UNION-ALL
(19c)
drop table tab1 purge;
create table tab1(
col1 int
,col2 int
,col3 int
,col4 int
);
drop table tab2 purge;
create table tab2(
col1 int
,col2 int
,col3 int
,col4 int
);
declare
begin
for i in 1..1000000 loop
insert into tab1 values(
i
,floor(dbms_random.value(1, 1001))
,floor(dbms_random.value(1, 1001))
,floor(dbms_random.value(1, 1000001))
);
insert into tab2 values(
i
,floor(dbms_random.value(1, 10001))
,floor(dbms_random.value(1, 10001))
,floor(dbms_random.value(1, 1000001))
);
end loop;
end;
/
commit;
select count(*) from tab1;
select * from tab1 order by dbms_random.value() fetch first 20 rows only;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
select count(*) from tab2;
select * from tab2 order by dbms_random.value() fetch first 20 rows only;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);
create index ind23 on tab2(col3);
create index ind24 on tab2(col4);
set time on
set timing on
exec dbms_stats.gather_table_stats(user,'TAB1');
exec dbms_stats.gather_table_stats(user,'TAB2');
explain plan for
select /*+ INDEX(t1) */ count(distinct t1.col1)
from tab1 t1
inner join tab2 t2
on ( t1.col2 = t2.col2 or t1.col3 = t2.col3 )
where t1.col4 < 1000
;
select * from table(dbms_xplan.display() );
→経過: 00:00:00.11
実行計画: UNION-ALL
※デフォルトでUNION-ALLの実行計画に変換される
explain plan for
select /*+ NO_EXPAND INDEX(t1) */ count(distinct t1.col1)
from tab1 t1
inner join tab2 t2
on ( t1.col2 = t2.col2 or t1.col3 = t2.col3 )
where t1.col4 < 1000
;
select * from table(dbms_xplan.display() );
→経過: 00:00:00.17
実行計画: BITMAP OR
explain plan for
select count(distinct t1.col1)
from (
select /*+ INDEX(t11) */ t11.col1
from tab1 t11
inner join tab2 t12
on ( t11.col2 = t12.col2 )
where t11.col4 < 1000
UNION ALL
select /*+ INDEX(t21) */ t21.col1
from tab1 t21
inner join tab2 t22
on ( t21.col3 = t22.col3 )
where t21.col4 < 1000
) t1;
select * from table(dbms_xplan.display() );
→経過: 00:00:00.11
実行計画: UNION-ALL
(14)
drop table tab1;
create table tab1(
col1 bigint
,col2 bigint
,col3 bigint
,col4 bigint
);
drop table tab2;
create table tab2(
col1 bigint
,col2 bigint
,col3 bigint
,col4 bigint
);
start transaction;
insert into tab1 select
g
,floor(random() * 1000)+1
,floor(random() * 1000)+1
,floor(random() * 1000000)+1
from generate_series(1,1000000) g;
commit;
start transaction;
insert into tab2 select
g
,floor(random() * 10000)+1
,floor(random() * 10000)+1
,floor(random() * 1000000)+1
from generate_series(1,1000000) g;
commit;
select count(*) from tab1;
select * from tab1 order by random() limit 20;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
select count(*) from tab2;
select * from tab2 order by random() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);
create index ind23 on tab2(col3);
create index ind24 on tab2(col4);
\timing 1
analyze tab1;
analyze tab2;
explain analyze
select count(distinct t1.col1)
from tab1 t1
inner join tab2 t2
on ( t1.col2 = t2.col2 or t1.col3 = t2.col3 )
where t1.col4 < 1000
;
→Execution Time: 185.689 ms
実行計画: BitmapOr
explain analyze
select count(distinct t1.col1)
from (
select t11.col1
from tab1 t11
inner join tab2 t12
on ( t11.col2 = t12.col2 )
where t11.col4 < 1000
UNION ALL
select t21.col1
from tab1 t21
inner join tab2 t22
on ( t21.col3 = t22.col3 )
where t21.col4 < 1000
) t1;
→Execution Time: 99.056 ms★
実行計画: Append
(2019)
drop table tab1;
create table tab1(
col1 bigint not null
,col2 bigint
,col3 bigint
,col4 bigint
);
drop table tab2;
create table tab2(
col1 bigint not null
,col2 bigint
,col3 bigint
,col4 bigint
);
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
insert into tab1 values(
@i
,floor(rand() * 1000)+1
,floor(rand() * 1000)+1
,floor(rand() * 1000000)+1
);
insert into tab2 values(
@i
,floor(rand() * 10000)+1
,floor(rand() * 10000)+1
,floor(rand() * 1000000)+1
);
set @i = @i + 1;
end
commit;
select count(*) from tab1;
select top 20 * from tab1 order by newid();
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
select count(*) from tab2;
select top 20 * from tab2 order by newid();
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);
create index ind23 on tab2(col3);
create index ind24 on tab2(col4);
set statistics time on
update statistics tab1;
update statistics tab2;
set statistics profile on
select count(distinct t1.col1)
from tab1 t1
inner join tab2 t2
on ( t1.col2 = t2.col2 or t1.col3 = t2.col3 )
where t1.col4 < 1000
;
→経過時間 = 967 ミリ秒
実行計画: Clustered Index Scan
select count(distinct t1.col1)
from (
select t11.col1
from tab1 t11
inner join tab2 t12
on ( t11.col2 = t12.col2 )
where t11.col4 < 1000
UNION ALL
select t21.col1
from tab1 t21
inner join tab2 t22
on ( t21.col3 = t22.col3 )
where t21.col4 < 1000
) t1;
set statistics profile off
→経過時間 = 38 ミリ秒★
実行計画: Concatenation
