外部結合による結合順の制約(HJ)

• MySQL

(8.0.22)

-- データ準備
drop table tab1;
drop table tab2;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

drop procedure proc1;
delimiter //
create procedure proc1(in p1 integer)
begin
declare i int;
set i = 1;
while i <= p1 do
insert into tab1 values(i, mod(i,10));
insert into tab2 values(i, mod(i,10));
set i = i + 1;
end while;
end
//
delimiter ;

call proc1(100000);

analyze table tab1;
analyze table tab2;

-- 確認

explain analyze
select /*+ HASH_JOIN(t1, t2) JOIN_ORDER(t2, t1) */
t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
;

explain analyze
select /*+ HASH_JOIN(t1, t2) JOIN_ORDER(t1, t2) */
t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
;

explain analyze
select /*+ HASH_JOIN(t1, t2) JOIN_ORDER(t2, t1) */
t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
left outer join tab2 t2
on t1.col1 = t2.col1
;

explain analyze
select /*+ HASH_JOIN(t1, t2) JOIN_ORDER(t1, t2) */
t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
left outer join tab2 t2
on t1.col1 = t2.col1
;

→左外部結合の右側テーブルはhash joinのbuild表にならない★

• Oracle

(19c)
http://oracle-performance-tuning-tips3-dass.blogspot.com/2011/03/hash-outer-right-join-oracle-tuning.html
https://blogs.oracle.com/otnjp/tsushima-hakushi-41

-- データ準備
drop table tab1;
drop table tab2;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

create unique index ind11 on tab1(col1);
create unique index ind21 on tab2(col1);

create index ind12 on tab1(col2);
create index ind22 on tab2(col2);

alter table tab1 add constraint tab1pk primary key(col1) using index ind11;
alter table tab2 add constraint tab2pk primary key(col1) using index ind21;

declare
begin
for i in 1..100000 loop
insert into tab1 values(i,mod(i,10));
insert into tab2 values(i,mod(i,10));
commit;
end loop;
end;
/

execute dbms_stats.gather_table_stats('TEST','TAB1');
execute dbms_stats.gather_table_stats('TEST','TAB2');

-- 確認
explain plan for
select /*+ USE_HASH(t1 t2) LEADING(t1 t2) */
t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

explain plan for
select /*+ USE_HASH(t1 t2) LEADING(t2 t1) */
t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

explain plan for
select /*+ USE_HASH(t1 t2) LEADING(t1 t2) */
t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
left outer join tab2 t2
on t1.col1 = t2.col1
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

explain plan for
select /*+ USE_HASH(t1 t2) SWAP_JOIN_INPUTS(t2) */
t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
left outer join tab2 t2
on t1.col1 = t2.col1
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

→左外部結合の右側テーブルはhash joinのbuild表になる
( LEADINGヒントでは確認できず、SWAP_JOIN_INPUTSヒントで確認できた。実行計画はHASH JOIN RIGHT OUTERとなる )

• PostgreSQL

(13)

-- データ準備
drop table tab1;
drop table tab2;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

create unique index ind11 on tab1(col1);
create unique index ind21 on tab2(col1);

create index ind12 on tab1(col2);
create index ind22 on tab2(col2);

alter table tab1 add constraint tab1pk primary key using index ind11;
alter table tab2 add constraint tab2pk primary key using index ind21;

insert into tab1 select generate_series(1,100000),mod(generate_series(1,100000),10);
insert into tab2 select generate_series(1,100000),mod(generate_series(1,100000),10);

analyze tab1;
analyze tab2;

-- 確認

explain analyze
/*+ HashJoin(t1 t2) Leading((t2 t1))*/
select t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
;

explain analyze
/*+ HashJoin(t1 t2) Leading((t1 t2))*/
select t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
;

explain analyze
/*+ HashJoin(t1 t2) Leading((t2 t1))*/
select t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
left outer join tab2 t2
on t1.col1 = t2.col1
;

explain analyze
/*+ HashJoin(t1 t2) Leading((t1 t2))*/
select t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
left outer join tab2 t2
on t1.col1 = t2.col1
;

→左外部結合の右側テーブルはhash joinのbuild表になる

• SQL Server

(2019)

-- データ準備
drop table tab1;
drop table tab2;

create table tab1(col1 int not null,col2 int);
create table tab2(col1 int not null,col2 int);

create index ind12 on tab1(col2);
create index ind22 on tab2(col2);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

declare @counter int;
set @counter = 1;

while (@counter <= 100000)
begin
insert into tab1 values (@counter,@counter%10);
insert into tab2 values (@counter,@counter%10);
set @counter = @counter + 1;
end
go

update statistics tab1;
update statistics tab2;

-- 確認
set showplan_text on
go

select t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
OPTION (HASH JOIN,FORCE ORDER)
;

select t1.col1,t1.col2,t2.col1,t2.col2
from tab2 t2
inner join tab1 t1
on t1.col1 = t2.col1
OPTION (HASH JOIN,FORCE ORDER)
;

select t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
left outer join tab2 t2
on t1.col1 = t2.col1
OPTION (HASH JOIN,FORCE ORDER)
;

select t1.col1,t1.col2,t2.col1,t2.col2
from tab1 t1
right outer join tab2 t2
on t1.col1 = t2.col1
OPTION (HASH JOIN,FORCE ORDER)
;

→右外部結合の左側テーブルがhash joinのbuild表になった

-- 追加調査

drop table tab3;
drop table tab4;

create table tab3(col1 int not null,col2 int);
create table tab4(col1 int not null,col2 int);

declare @counter int;
set @counter = 1;

while (@counter <= 100000)
begin
insert into tab3 values (@counter,@counter%10);
set @counter = @counter + 1;
end
go

declare @counter int;
set @counter = 1;
while (@counter <= 1000)
begin
insert into tab4 values (@counter,@counter%10);
set @counter = @counter + 1;
end
go

update statistics tab3;
update statistics tab4;

select t1.col1,t1.col2,t2.col1,t2.col2
from tab3 t1
left outer join tab4 t2
on t1.col1 = t2.col1
OPTION (HASH JOIN)
;

→左外部結合の右側テーブルはhash joinのbuild表になる