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

• MySQL

(8.0.18)

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

create table tab1(col1 int,col2 int);
create table tab2(col1 int,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);

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 /*+ NO_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 /*+ NO_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 /*+ NO_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
;

explain analyze
select /*+ NO_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
;

→左外部結合の右側テーブルはnested loopの駆動表にならない

※straight_join構文の場合、左外部結合の右側テーブルはnested loopの駆動表になっているように見える

• Oracle

(19c)

-- データ準備
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_NL(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_NL(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_NL(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_NL(t1 t2) LEADING(t2 t1) */
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') );

→左外部結合の右側テーブルはnested loopの駆動表にならない

• 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
/*+ NestLoop(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
/*+ NestLoop(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
/*+ NestLoop(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
;

explain analyze
/*+ NestLoop(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
;

→左外部結合の右側テーブルはnested loopの駆動表にならない

• 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 (LOOP 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 (LOOP 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 (LOOP 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 (LOOP JOIN,FORCE ORDER)
;

→下記エラーとなったので左外部結合の右側テーブルはnested loopの駆動表にならないと思われる

メッセージ 8622、レベル 16、状態 1、行 1
このクエリに定義されたヒントにより、クエリ プロセッサはクエリ プランを作成できませんでした。ヒントを何も指定しないで、SET FORCEPLAN を使用しないでクエリを再実行してください。