インラインビューと結合ヒント句

(8.0.26)

https://dev.mysql.com/doc/refman/8.0/ja/hash-joins.html
https://qiita.com/hmatsu47/items/e473a3e566b910d61f5b


-- 1. テストデータ作成

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
   );

drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 bigint
   );

drop table tab3;
create table tab3(
    col1 bigint 
   ,col2 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() * 10000)+1
    );
    insert into tab2 values(
     i
    ,floor(rand() * 10000)+1
    );
    insert into tab3 values(
     i
    ,floor(rand() * 10000)+1
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(100000);

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

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

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

 

analyze table tab1;
analyze table tab2;
analyze table tab3;

 

--2. 動作確認

explain 
select /*+ JOIN_PREFIX(t31@qb2, t32@qb2)  */  count(*) from tab1 t1
inner join
(
 select /*+ QB_NAME(qb1) NO_JOIN_INDEX(t21) NO_JOIN_INDEX(t22) */  t21.col2 from tab2 t21 inner join tab3 t22
 on t21.col1 = t22.col1
 where t21.col2 < 5000
 group by t21.col2
 having count(t22.col2) > 100
) t2
on t1.col2 = t2.col2
inner join
(
 select /*+ QB_NAME(qb2) NO_JOIN_INDEX(t31) NO_JOIN_INDEX(t32)   BNL(t31, t32) */  t31.col2 from tab2 t31 inner join tab3 t32
 on t31.col1 = t32.col1
) t3
on t1.col2 = t3.col2
;

show warnings;


MySQL 8.0.18 以降、MySQL では、各結合に等価結合条件があり、
結合条件に適用できるインデックスがないクエリーに対してハッシュ結合が使用されます

MySQL 8.0.20 以降では、ブロックネストループのサポートが削除され、
以前にブロックネステッドループが使用されていた場所では、サーバーはハッシュ結合を採用します。

結合ヒント句の挙動を調べるため、NO_JOIN_INDEXでhash joinにした状態から
NO_BNLヒントの付与してNested Loopにもどすことでヒント句の効果を確認する


-- 問合せブロック付きヒント


/*+ JOIN_PREFIX(t1, t2) NO_BNL(t1, t21@qb1)  */  → 実行計画変化なし
/*+ JOIN_PREFIX(t1, t31@qb2) NO_BNL(t1, t31@qb2)  */  → OK

 

-- インラインビュー名を指定

/*+ JOIN_PREFIX(t1, t2)  NO_BNL(t1, t2) */    → 実行計画変化なし
/*+ JOIN_PREFIX(t1, t31@qb2)  NO_BNL(t1, t3) */  → 実行計画変化なし

 


-- インラインビュー内部名を本体に指定

/*+ JOIN_PREFIX(t1, t2)  NO_BNL(t1, t21) */       → Unresolved の警告 
/*+ JOIN_PREFIX(t1, t31@qb2)  NO_BNL(t1, t31) */  → Unresolved の警告  


--インラインビュー内部名をインラインビューに指定

/*+ NO_BNL(t21, t22) */  → OK
/*+ NO_BNL(t31, t32) */  → OK

 

 

 

(19c)

https://ameblo.jp/keshi0414/entry-12318306543.html
https://qiita.com/mkyz08/items/9dfa4a5ea636238afc3e
https://blogs.oracle.com/otnjp/post/tsushima-hakushi-55

 


-- 1. テストデータ作成

drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 int
  );

drop table tab2 purge;
create table tab2(
    col1 int 
   ,col2 int
   );

drop table tab3 purge;
create table tab3(
    col1 int 
   ,col2 int
   );

declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
    i
   ,floor(dbms_random.value(1, 10001) )
   );
  
  insert into tab2 values(
    i
   ,floor(dbms_random.value(1, 10001) )
   );
  insert into tab3 values(
    i
   ,floor(dbms_random.value(1, 10001) )
   );
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);

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

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);
create index ind32 on tab3(col2);

 

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

 


--2. 動作確認

 

[1]ANSI準拠の結合文


explain plan for
select /*+ LEADING(t1 T31@SEL$5) USE_HASH( T3 ) */     count(*) from tab1 t1
inner join
(
 select /* USE_HASH( T22 ) */  t21.col2 from tab2 t21 inner join tab3 t22
 on t21.col1 = t22.col1
 where t21.col2 < 5000
 group by t21.col2
 having count(t22.col2) > 100
) t2
on t1.col2 = t2.col2
inner join
(
 select /* USE_HASH( T32 ) */    t31.col2 from tab2 t31 inner join tab3 t32
 on t31.col1 = t32.col1
) t3
on t1.col2 = t3.col2
;

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


--1.1 問合せブロック付きヒント(システム生成)

/*+ LEADING(t1 T31@SEL$5) USE_NL( T31@SEL$5 ) */ →OK
/*+ USE_HASH( T21@SEL$2) */  →Unresolved

グループ化していないインラインビュー内のテーブルは結合ヒント句に指定可能
グループ化したインラインビュー内のテーブルは結合ヒント句に指定不可能

 

--1.2 問合せブロック付きヒント(ユーザ指定)
/*+ LEADING(t1 T31@qb2) USE_NL( T31@qb2 ) */  →Unresolved ★
/*+ USE_HASH( T21@qb1 ) */  →Unresolved


--1.3 グローバル・ヒント
/*+ LEADING(t1 T31@SEL$5) USE_NL(t3.t31 ) */   →グローバルヒントが認識されない ★

 

--1.4 インラインビュー名を指定

/*+ LEADING(t1 T31@SEL$5) USE_NL( T3 ) */   →Unused
/*+ USE_HASH( T2 ) */   →OK

グループ化していないインラインビューは結合ヒント句に指定不可能
グループ化したインラインビューは結合ヒント句に指定可能

--1.5 インラインビュー内部名を本体に指定

/*+ LEADING(t1 T31@SEL$5) USE_NL( T31 ) */  →Unresolved 
/*+ USE_HASH( T21 ) */    →Unresolved 

--1.6 インラインビュー内部名をインラインビューに指定

/*+ USE_HASH( T32 ) */   →OK
/*+ USE_HASH( T22 ) */   →OK


[2]Oracle固有の結合文


explain plan for
select /* USE_HASH( T21 ) */   count(*) from tab1 t1
,(
 select /*+ USE_HASH( T22 ) */ t21.col2 from tab2 t21 , tab3 t22
 where t21.col1 = t22.col1
 and t21.col2 < 5000
 group by t21.col2
 having count(t22.col2) > 100
) t2
,(
 select /*+ USE_HASH( T32 ) */  t31.col2 from tab2 t31 , tab3 t32
 where t31.col1 = t32.col1
) t3
where t1.col2 = t2.col2
and t1.col2 = t3.col2
;

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

--2.1 問合せブロック付きヒント(システム生成)

/*+ LEADING(t1 T31@SEL$3) USE_NL( T31@SEL$3 ) */  →OK
/*+ USE_HASH( T21@SEL$2 ) */  →Unresolved

グループ化していないインラインビュー内のテーブルは結合ヒント句に指定可能
グループ化したインラインビュー内のテーブルは結合ヒント句に指定不可能

 

--2.2 問合せブロック付きヒント(ユーザ指定)
/*+ LEADING(t1 T31@qb2) USE_NL( T31@qb2 ) */   →OK
/*+ USE_HASH( T21@qb1 ) */  →Unresolved


--2.3 グローバル・ヒント
 /*+ LEADING(t1 T31@SEL$3) USE_NL(t3.t31 ) */   →OK

 

--2.4 インラインビュー名を指定

/*+ LEADING(t1 T31@SEL$3) USE_NL( T3 ) */ →Unused
/*+ USE_HASH( T2 ) */   →OK

グループ化していないインラインビューは結合ヒント句に指定不可能
グループ化したインラインビューは結合ヒント句に指定可能


--2.5 インラインビュー内部名を本体に指定

/*+ LEADING(t1 T31@SEL$3) USE_NL( T31 ) */  →Unresolved 
/*+ USE_HASH( T21 ) */    →Unresolved 

--2.6 インラインビュー内部名をインラインビューに指定

/*+ USE_HASH( T32 ) */   →OK
/*+ USE_HASH( T22 ) */   →OK

 

(14)

-- 1. テストデータ作成

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
  );
drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 bigint
   );
   
drop table tab3;
create table tab3(
    col1 bigint 
   ,col2 bigint
   );

start transaction;
insert into tab1 select
   g
  ,floor(random() * 10000)+1
from generate_series(1,1000000) g;

commit;

start transaction;
insert into tab2 select
   g
  ,floor(random() * 10000)+1
from generate_series(1,1000000) g;

commit;

start transaction;
insert into tab3 select
   g
  ,floor(random() * 10000)+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);
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);
select count(*) from tab3;
select * from tab3 order by random() limit 20;
alter table tab3 add constraint tab3pk primary key (col1);
create index ind32 on tab3(col2);

 


\timing 1
\pset pager 0
set pg_hint_plan.debug_print = on;

analyze tab1;
analyze tab2;
analyze tab3;


--2. 動作確認

 

/*+ HashJoin(t2 t1) */ 
explain 
select  count(*) from tab1 t1
inner join
(
 select t21.col2 from tab2 t21 inner join tab3 t22
 on t21.col1 = t22.col1
 where t21.col2 < 5000
 group by t21.col2
 having count(t22.col2) > 100
) t2
on t1.col2 = t2.col2
inner join
(
 select  t31.col2 from tab2 t31 inner join tab3 t32
 on t31.col1 = t32.col1
) t3
on t1.col2 = t3.col2
;


-- インラインビュー名を指定
/*+ MergeJoin(t1 t3) */   → not used hint
/*+ HashJoin(t2 t1) */   → used hint 


グループ化していないインラインビューは結合ヒント句に指定不可能
グループ化したインラインビューは結合ヒント句に指定可能

-- インラインビュー内部名を本体に指定

/*+ Leading(t1 t31) NestLoop(t1 t31) */   → used hint
/*+ HashJoin(t1 t21) */     → not used hint

グループ化していないインラインビュー内のテーブルは結合ヒント句に指定可能
グループ化したインラインビュー内のテーブルは結合ヒント句に指定不可能

(2019)
SQL Serverは調査観点なし