SQLチューニング(共通部分切り出し)

with句での切り出しについては
実行計画変化なし(MySQL,SQL Server)
あまり効果なし(Oracle)
逆に悪化(PostgreSQL)

• MySQL

(8.0.26)

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab3;
create table tab3(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab4;
create table tab4(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab5;
create table tab5(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

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
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    );
    insert into tab2 values(
     i
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    );
    insert into tab3 values(
     i
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    );
    insert into tab4 values(
     i
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    );
    insert into tab5 values(
     i
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    );

  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);

select count(*) from tab2;
select * from tab2 order by rand() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);

select count(*) from tab3;
select * from tab3 order by rand() limit 20;
alter table tab3 add constraint tab3pk primary key (col1);

select count(*) from tab4;
select * from tab4 order by rand() limit 20;
alter table tab4 add constraint tab4pk primary key (col1);

select count(*) from tab5;
select * from tab5 order by rand() limit 20;
alter table tab5 add constraint tab5pk primary key (col1);

analyze table tab1;
analyze table tab2;
analyze table tab3;
analyze table tab4;
analyze table tab5;

-- 1. 切り出しなし

explain format=tree 
select t1.col1,t2.col5 col2,t3.col5 col3,t4.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join tab4 t4 on t1.col1 = t4.col1
UNION ALL
select t1.col1,t2.col5 col2,t3.col5 col3,t5.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join tab5 t5 on t1.col1 = t5.col1
order by col4 limit 1
;

38.47 sec

-- 2. with句で共通部分切り出し

explain format=tree 
with t0 as (
select t1.col1,t2.col5 col2,t3.col5 col3
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
)
select t0.col1,t0.col2,t0.col3,t4.col5 col4
from t0
inner join tab4 t4 on t0.col1 = t4.col1
UNION ALL
select t0.col1,t0.col2,t0.col3,t5.col5 col4
from t0
inner join tab5 t5 on t0.col1 = t5.col1
order by col4 limit 1
;

※with句で切り出しても実行計画変化なし

38.35 sec

-- 3. 差異部分をインラインビュー

explain format=tree 
select t1.col1,t2.col5 col2,t3.col5 col3,t4.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join 
( select t41.col1,t41.col5 from tab4 t41
  UNION ALL
  select t42.col1,t42.col5 from tab5 t42
) t4
on t1.col1 = t4.col1
order by col4 limit 1
;

15.03 sec

• Oracle

(19c)

drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 varchar2(1000)
   ,col3 varchar2(1000)
   ,col4 varchar2(1000)
   ,col5 varchar2(1000)
  );

drop table tab2 purge;
create table tab2(
    col1 int 
   ,col2 varchar2(1000)
   ,col3 varchar2(1000)
   ,col4 varchar2(1000)
   ,col5 varchar2(1000)
  );

drop table tab3 purge;
create table tab3(
    col1 int 
   ,col2 varchar2(1000)
   ,col3 varchar2(1000)
   ,col4 varchar2(1000)
   ,col5 varchar2(1000)
  );

drop table tab4 purge;
create table tab4(
    col1 int 
   ,col2 varchar2(1000)
   ,col3 varchar2(1000)
   ,col4 varchar2(1000)
   ,col5 varchar2(1000)
  );

drop table tab5 purge;
create table tab5(
    col1 int 
   ,col2 varchar2(1000)
   ,col3 varchar2(1000)
   ,col4 varchar2(1000)
   ,col5 varchar2(1000)
  );

declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
    i
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   );
  
  insert into tab2 values(
    i
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   );

  insert into tab3 values(
    i
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   );

  insert into tab4 values(
    i
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   );

  insert into tab5 values(
    i
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   );

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);

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);

select count(*) from tab3;
select * from tab3 order by dbms_random.value()  fetch first 20 rows only;
alter table tab3 add constraint tab3pk primary key (col1);

select count(*) from tab4;
select * from tab4 order by dbms_random.value()  fetch first 20 rows only;
alter table tab4 add constraint tab4pk primary key (col1);

select count(*) from tab5;
select * from tab5 order by dbms_random.value()  fetch first 20 rows only;
alter table tab5 add constraint tab5pk primary key (col1);

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');
exec dbms_stats.gather_table_stats(user,'TAB5');

-- 1. 切り出しなし

explain plan for
select t1.col1,t2.col5 col2,t3.col5 col3,t4.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join tab4 t4 on t1.col1 = t4.col1
UNION ALL
select t1.col1,t2.col5 col2,t3.col5 col3,t5.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join tab5 t5 on t1.col1 = t5.col1
order by col4 fetch first 1 rows only
;

select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:12.12

-- 2. with句で共通部分切り出し

explain plan for
with t0 as (
select t1.col1,t2.col5 col2,t3.col5 col3
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
)
select t0.col1,t0.col2,t0.col3,t4.col5 col4
from t0
inner join tab4 t4 on t0.col1 = t4.col1
UNION ALL
select t0.col1,t0.col2,t0.col3,t5.col5 col4
from t0
inner join tab5 t5 on t0.col1 = t5.col1
order by col4 fetch first 1 rows only
;

select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:10.93

-- 3. 差異部分をインラインビュー

explain plan for
select t1.col1,t2.col5 col2,t3.col5 col3,t4.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join 
( select t41.col1,t41.col5 from tab4 t41
  UNION ALL
  select t42.col1,t42.col5 from tab5 t42
) t4
on t1.col1 = t4.col1
order by col4 fetch first 1 rows only
;

select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:07.38

• PostgreSQL

(14)

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );
   
drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );
   
drop table tab3;
create table tab3(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );
   
drop table tab4;
create table tab4(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );
   
drop table tab5;
create table tab5(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );
   

start transaction;
insert into tab1 select
   g
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;
commit;

start transaction;
insert into tab2 select
   g
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;
commit;

start transaction;
insert into tab3 select
   g
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;
commit;

start transaction;
insert into tab4 select
   g
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;
commit;

start transaction;
insert into tab5 select
   g
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;
commit;

\pset pager 0

select count(*) from tab1;
select * from tab1 order by random() limit 20;
alter table tab1 add constraint tab1pk primary key (col1);

select count(*) from tab2;
select * from tab2 order by random() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);

select count(*) from tab3;
select * from tab3 order by random() limit 20;
alter table tab3 add constraint tab3pk primary key (col1);

select count(*) from tab4;
select * from tab4 order by random() limit 20;
alter table tab4 add constraint tab4pk primary key (col1);

select count(*) from tab5;
select * from tab5 order by random() limit 20;
alter table tab5 add constraint tab5pk primary key (col1);

\timing 1
set pg_hint_plan.debug_print = on;

analyze tab1;
analyze tab2;
analyze tab3;
analyze tab4;
analyze tab5;

-- 1. 切り出しなし

explain 
select t1.col1,t2.col5 col2,t3.col5 col3,t4.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join tab4 t4 on t1.col1 = t4.col1
UNION ALL
select t1.col1,t2.col5 col2,t3.col5 col3,t5.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join tab5 t5 on t1.col1 = t5.col1
order by col4 limit 1
;

00:04.231

-- 2. with句で共通部分切り出し

explain 
with t0 as (
select t1.col1,t2.col5 col2,t3.col5 col3
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
)
select t0.col1,t0.col2,t0.col3,t4.col5 col4
from t0
inner join tab4 t4 on t0.col1 = t4.col1
UNION ALL
select t0.col1,t0.col2,t0.col3,t5.col5 col4
from t0
inner join tab5 t5 on t0.col1 = t5.col1
order by col4 limit 1
;

00:07.055
やや悪化

-- 3. 差異部分をインラインビュー

explain 
select t1.col1,t2.col5 col2,t3.col5 col3,t4.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join 
( select t41.col1,t41.col5 from tab4 t41
  UNION ALL
  select t42.col1,t42.col5 from tab5 t42
) t4
on t1.col1 = t4.col1
order by col4 limit 1
;

00:01.836

• SQL Server

(2019)

drop table tab1;
create table tab1(
    col1 bigint not null
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab2;
create table tab2(
    col1 bigint not null
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab3;
create table tab3(
    col1 bigint not null
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab4;
create table tab4(
    col1 bigint not null
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab5;
create table tab5(
    col1 bigint not null
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 100000
begin
  insert into tab1 values(
     @i
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    );
  insert into tab2 values(
     @i
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    );
  insert into tab3 values(
     @i
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    );
  insert into tab4 values(
     @i
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    );
  insert into tab5 values(
     @i
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    );
 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);

select count(*) from tab2;
select top 20 * from tab2 order by newid();
alter table tab2 add constraint tab2pk primary key (col1);

select count(*) from tab3;
select top 20 * from tab3 order by newid();
alter table tab3 add constraint tab3pk primary key (col1);

select count(*) from tab4;
select top 20 * from tab4 order by newid();
alter table tab4 add constraint tab4pk primary key (col1);

select count(*) from tab5;
select top 20 * from tab5 order by newid();
alter table tab5 add constraint tab5pk primary key (col1);

set statistics time on

update statistics tab1;
update statistics tab2;
update statistics tab3;
update statistics tab4;
update statistics tab5;

-- 1. 切り出しなし

select t1.col1,t2.col5 col2,t3.col5 col3,t4.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join tab4 t4 on t1.col1 = t4.col1
UNION ALL
select t1.col1,t2.col5 col2,t3.col5 col3,t5.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join tab5 t5 on t1.col1 = t5.col1
order by col4 offset 0 rows fetch next 1 rows only
;

経過時間 = 1220 ミリ秒

-- 2. with句で共通部分切り出し

with t0 as (
select t1.col1,t2.col5 col2,t3.col5 col3
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
)
select t0.col1,t0.col2,t0.col3,t4.col5 col4
from t0
inner join tab4 t4 on t0.col1 = t4.col1
UNION ALL
select t0.col1,t0.col2,t0.col3,t5.col5 col4
from t0
inner join tab5 t5 on t0.col1 = t5.col1
order by col4 offset 0 rows fetch next 1 rows only
;

※with句で切り出しても実行計画変化なし
経過時間 = 1228 ミリ秒

-- 3. 差異部分をインラインビュー

select t1.col1,t2.col5 col2,t3.col5 col3,t4.col5 col4
from tab1 t1 
inner join tab2 t2 on t1.col1 = t2.col1
inner join tab3 t3 on t1.col1 = t3.col1
inner join 
( select t41.col1,t41.col5 from tab4 t41
  UNION ALL
  select t42.col1,t42.col5 from tab5 t42
) t4
on t1.col1 = t4.col1
order by col4 offset 0 rows fetch next 1 rows only
;

経過時間 = 725 ミリ秒