サブクエリと結合ヒント句

• MySQL

(8.0.26)

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() * 100000)+1
    );
    insert into tab2 values(
     i
    ,floor(rand() * 100000)+1
    );
    insert into tab3 values(
     i
    ,floor(rand() * 100000)+1
    );
  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);
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 /* NO_BNL(t1, t5)  */     count(*) from tab1 t1
where exists ( select 1 from
  (
   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 where t1.col2 = t2.col2 )
and exists ( select 1 from
  (
   select /*+ QB_NAME(qb2) NO_JOIN_INDEX(t31) JOIN_INDEX(t32)  */   t31.col2 from tab2 t31 inner join tab3 t32
   on t31.col1 = t32.col1
  ) t3 where t1.col2 = t3.col2 )
and exists ( select /*+ QB_NAME(qb3) NO_JOIN_INDEX(t4)  */  1 from tab1 t4 where t1.col1 = t4.col2)
and exists ( select /*+ QB_NAME(qb4) NO_JOIN_INDEX(t5) NO_JOIN_INDEX(t6) BNL(t5,t6)    */    1 from tab1 t5 inner join tab2 t6 on t5.col1 = t6.col1 where t1.col1 = t5.col1 and t1.col2 = t6.col2)
;

show warnings;

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

/*+ NO_BNL(t1, t21@qb1) */             → 実行計画変化なし
/*+ NO_BNL(t1, t31@qb2) */             → 実行計画変化なし
/*+ NO_BNL(t1, t4@qb3)  */             → 実行計画変化なし
/*+ NO_BNL(t1, t5@qb4)  */             → 実行計画変化なし

-- サブクエリ名を指定

/*+ NO_BNL(t1, t2) */             → Unresolved
/*+ NO_BNL(t1, t3) */             → Unresolved

-- サブクエリ内部名を本体に指定

/*+ NO_BNL(t1, t21) */             → Unresolved
/*+ NO_BNL(t1, t31) */             → Unresolved
/*+ NO_BNL(t1, t4)  */             → Unresolved
/*+ NO_BNL(t1, t5)  */             → Unresolved

-- サブクエリ内部名をサブクエリに指定

/*+ NO_BNL(t21,t22) */             → OK
/*+ NO_BNL(t31,t32) */             → 実行計画変化なし
/*+ NO_BNL(t5,t6)  */              → 実行計画変化なし

• Oracle

(19c)

-- 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, 100001) )
   );
  
  insert into tab2 values(
    i
   ,floor(dbms_random.value(1, 100001) )
   );
  insert into tab3 values(
    i
   ,floor(dbms_random.value(1, 100001) )
   );
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. 動作確認

explain plan for
select   /* USE_HASH(T5) */   count(*) from tab1 t1
where exists ( select 1 from
  (
   select /* QB_NAME(qb1) */ /* USE_MERGE(t21 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 where t1.col2 = t2.col2 )
and exists ( select 1 from
  (
   select /* QB_NAME(qb2) */ /* USE_MERGE(t31 t32 ) */     t31.col2 from tab2 t31 inner join tab3 t32
   on t31.col1 = t32.col1
  ) t3 where t1.col2 = t3.col2 )
and exists ( select /* QB_NAME(qb3) */ /*+ USE_MERGE(t4 ) */      1 from tab1 t4 where t1.col1 = t4.col2)
and exists ( select /* QB_NAME(qb4) */ /* USE_NL(t5 t6) */      1 from tab1 t5 inner join tab2 t6 on t5.col1 = t6.col1 where t1.col1 = t5.col1 and t1.col2 = t6.col2)
;

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

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

/*+ USE_HASH(T21@SEL$4)  */       → Unresolved
/*+ USE_HASH(T31@SEL$7) */        → Unresolved
/*+ USE_HASH(T4@SEL$9) */         → OK
/*+ USE_HASH(T5@SEL$1) */         → Unresolved

シンプルなサブクエリは結合ヒント句指定可能

--1.2 問合せブロック付きヒント(ユーザ指定)

/*+ USE_HASH(T21@QB1)  */       → Unresolved
/*+ USE_HASH(T31@QB2) */        → Unresolved
/*+ USE_HASH(T4@QB3) */         → OK
/*+ USE_HASH(T5@QB4) */         → Unresolved

--1.3 グローバル・ヒント
/*+ USE_HASH(T2.T21)  */       → グローバルヒントが認識されない 
/*+ USE_HASH(T3.T31) */        → グローバルヒントが認識されない 

--1.4 サブクエリ名を指定

/*+ USE_HASH(T2) */          → Unresolved
/*+ USE_HASH(T3) */          → Unresolved

--1.5 サブクエリ内部名を本体に指定

/*+ USE_HASH(T21)  */       → Unresolved
/*+ USE_HASH(T31) */        → Unresolved
/*+ USE_HASH(T4) */         → Unresolved
/*+ USE_HASH(T5) */         → Unresolved

--1.6 サブクエリ内部名をサブクエリに指定

/*+ LEADING(t21 t22) USE_MERGE(t22)  */     → OK
/*+ LEADING(t31) USE_MERGE(t32 ) */         → OK
/*+ USE_MERGE(t4 ) */                       → Unused ★
/*+ LEADING(t6 t5) USE_NL(t5) */            → OK

• PostgreSQL

(14)
https://github.com/ossc-db/pg_hint_plan#hints-list

-- 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() * 100000)+1
from generate_series(1,1000000) g;

commit;

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

commit;

start transaction;
insert into tab3 select
   g
  ,floor(random() * 100000)+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. 動作確認

set pg_hint_plan.debug_print=1;
show pg_hint_plan.debug_print;

/*+ HashJoin(t1 ANY_subquery) */ 
explain
select    count(*) from tab1 t1
where exists ( select 1 from
  (
   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 where t1.col2 = t2.col2 )
and exists ( select 1 from
  (
   select  t31.col2 from tab2 t31 inner join tab3 t32
   on t31.col1 = t32.col1
  ) t3 where t1.col2 = t3.col2 )
and exists ( select 1 from tab1 t4 where t1.col1 = t4.col2)
and exists ( select  1 from tab1 t5 inner join tab2 t6 on t5.col1 = t6.col1 where t1.col1 = t5.col1 and t1.col2 = t6.col2)
;

-- サブクエリ名を指定
/*+ MergeJoin(t1 t2) */   → used hintと表示されるが実行計画変化なし
/*+ HashJoin(t1 t3) */    →  not used hint

-- サブクエリ内部名を本体に指定

/*+ MergeJoin(t1 t21) */   → not used hint
/*+ HashJoin(t1 t31) */   →  used hintと表示されるが実行計画変化なし
/*+ HashJoin(t1 t4) */   →  used hintと表示されるが実行計画変化なし
/*+ HashJoin(t1 t5) */   →  used hintと表示されるが実行計画変化なし 

-- ANY_subqueryキーワードの使用

/*+ HashJoin(t1 ANY_subquery) */   → not used hint

exists句に未対応の模様

• SQL Server

(2019)

調査観点なし