3テーブルHJの結合順制御

DB

(8.0.22)
調べた限り、Left-deep JoinとRight-deep Joinを制御する方法なし

※Auroraにはヒント句がある模様

HASH_JOIN_PROBING、NO_HASH_JOIN_PROBING
HASH_JOIN_BUILDING、NO_HASH_JOIN_BUILDING

(19c)

https://blogs.oracle.com/otnjp/tsushima-hakushi-46

SWAP_JOIN_INPUTSヒント -> Build表にする
NO_SWAP_JOIN_INPUTSヒント -> Probe表にする

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

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

 

declare
begin
for i in 1..100000 loop
insert into tab1 values(i,mod(i,10));
insert into tab2 values(i,mod(i,10));
insert into tab3 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');
execute dbms_stats.gather_table_stats('TEST','TAB3');


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

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

 

全結合パターンは12通りあり、ヒント句は以下の通り

(12)3 USE_HASH(t2 t3) LEADING(t1 t2 t3) NO_SWAP_JOIN_INPUTS(t3)
1(23) USE_HASH(t3 t1) LEADING(t2 t3 t1) SWAP_JOIN_INPUTS(t1)

(13)2 USE_HASH(t3 t2) LEADING(t1 t3 t2) NO_SWAP_JOIN_INPUTS(t2)
1(32) USE_HASH(t2 t1) LEADING(t3 t2 t1) SWAP_JOIN_INPUTS(t1)

(21)3 USE_HASH(t1 t3) LEADING(t2 t1 t3) NO_SWAP_JOIN_INPUTS(t3)
2(13) USE_HASH(t3 t2) LEADING(t1 t3 t2) SWAP_JOIN_INPUTS(t2)

(23)1 USE_HASH(t3 t1) LEADING(t2 t3 t1) NO_SWAP_JOIN_INPUTS(t1)
2(31) USE_HASH(t1 t2) LEADING(t3 t1 t2) SWAP_JOIN_INPUTS(t2)

(31)2 USE_HASH(t1 t2) LEADING(t3 t1 t2) NO_SWAP_JOIN_INPUTS(t2)
3(12) USE_HASH(t2 t3) LEADING(t1 t2 t3) SWAP_JOIN_INPUTS(t3)

(32)1 USE_HASH(t2 t1) LEADING(t3 t2 t1) NO_SWAP_JOIN_INPUTS(t1)
3(21) USE_HASH(t1 t3) LEADING(t2 t1 t3) SWAP_JOIN_INPUTS(t3)


(備考)
NLについては、Left-deep Joinのみで、Right-deep Joinはない

 

(13)

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

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


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);
insert into tab3 select generate_series(1,100000),mod(generate_series(1,100000),10);


analyze tab1;
analyze tab2;
analyze tab3;


-- 確認

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

 

全結合パターンは12通りあり、ヒント句は以下の通り

(12)3 Leading(( (t1 t2) t3 )) HashJoin(t1 t2) HashJoin(t1 t2 t3)
1(23) Leading(( t1 (t2 t3) )) HashJoin(t2 t3) HashJoin(t1 t2 t3)

(13)2 Leading(( (t1 t3) t2 )) HashJoin(t1 t3) HashJoin(t1 t3 t2)
1(32) Leading(( t1 (t3 t2) )) HashJoin(t3 t2) HashJoin(t1 t3 t2)

(21)3 Leading(( (t2 t1) t3 )) HashJoin(t2 t1) HashJoin(t2 t1 t3)
2(13) Leading(( t2 (t1 t3) )) HashJoin(t1 t3) HashJoin(t2 t1 t3)

(23)1 Leading(( (t2 t3) t1 )) HashJoin(t2 t3) HashJoin(t2 t3 t1)
2(31) Leading(( t2 (t3 t1) )) HashJoin(t3 t1) HashJoin(t2 t3 t1)

(31)2 Leading(( (t3 t1) t2 )) HashJoin(t3 t1) HashJoin(t3 t1 t2)
3(12) Leading(( t3 (t1 t2) )) HashJoin(t1 t2) HashJoin(t3 t1 t2)

(32)1 Leading(( (t3 t2) t1 )) HashJoin(t3 t2) HashJoin(t3 t2 t1)
3(21) Leading(( t3 (t2 t1) )) HashJoin(t2 t1) HashJoin(t3 t2 t1)

 

(備考)
NLについても、ヒント句でLeft-deep JoinとRight-deep Joinの制御は可能。実用性は全くないが。

 

(2019)
調べた限り、Left-deep JoinとRight-deep Joinを制御する方法なし