3テーブル結合ヒント句指定方法

DB

(8.0.31)

 

-- データ準備
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);


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,mod(i,10) );
    insert into tab2 values(i,mod(i,10) );
    insert into tab3 values(i,mod(i,10) );
  end while;
  commit;
end
//
delimiter ;

call proc1(100000);

 

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


-- 確認

explain
select /*+ JOIN_PREFIX(t1, t2, t3) NO_BNL(t1, t2, 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
;

show warnings;

explain format=tree
select /*+ JOIN_PREFIX(t1, t2, t3) NO_BNL(t1, t2, 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
;

-- 結果
/*+ JOIN_PREFIX(t1, t2, t3) NO_BNL(t2) NO_BNL(t3) */           => OK
/*+ JOIN_PREFIX(t1, t2, t3) NO_BNL(t2, t3) */                  => OK

結合先のテーブルを指定する
結合ヒント句のテーブルは一括指定と分割指定で挙動は同じ

 

 

 

(19c)

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


-- 結果
/*+ LEADING(t1 t2 t3) USE_HASH(t2 t3) */              => OK
/*+ LEADING(t1 t2 t3) USE_HASH(t2) USE_HASH(t3) */    => OK

/*+ LEADING(t1 t2 t3) USE_NL(t2 t3) */            => OK
/*+ LEADING(t1 t2 t3) USE_NL(t2) USE_NL(t3) */    => OK

結合先のテーブルを指定する
結合ヒント句のテーブルは一括指定と分割指定で挙動は同じ

 

(15)

-- pg_hint_planインストール

sudo dnf install git
sudo dnf install rpm-build

git clone https://github.com/ossc-db/pg_hint_plan.git
cd pg_hint_plan

make PG_CONFIG=/usr/pgsql-15/bin/pg_config
sudo -E make PG_CONFIG=/usr/pgsql-15/bin/pg_config install

 

CREATE EXTENSION pg_hint_plan;

vim postgresql.conf
shared_preload_libraries = 'pg_hint_plan'

sudo systemctl restart postgresql-15


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

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

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

analyze tab1;
analyze tab2;
analyze tab3;


-- 確認

explain 
/*+ Leading(( (t1 t2) t3 ) ) HashJoin(t1 t2) HashJoin(t1 t2 t3) */ 
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
;


-- 結果
/*+ Leading(( (t1 t2) t3 ) ) HashJoin(t1 t2) HashJoin(t1 t2 t3) */       => OK
/*+ Leading(( (t1 t2) t3 ) ) HashJoin(t1 t2) HashJoin(t2 t3) */          => NG
/*+ Leading(( (t1 t2) t3 ) ) HashJoin(t1 t2) HashJoin(t1 t3) */          => NG
/*+ Leading(( (t1 t2) t3 ) ) HashJoin(t2) HashJoin(t3) */                => NG

/*+ Leading(( (t1 t2) t3 ) ) NestLoop(t1 t2) NestLoop(t1 t2 t3) */       => OK
/*+ Leading(( (t1 t2) t3 ) ) NestLoop(t1 t2) NestLoop(t2 t3) */          => NG
/*+ Leading(( (t1 t2) t3 ) ) NestLoop(t1 t2) NestLoop(t1 t3) */          => NG
/*+ Leading(( (t1 t2) t3 ) ) NestLoop(t2) NestLoop(t3) */                => NG


結合元と結合先のテーブルを指定する
3テーブル以上の場合は再帰的に関連テーブルをすべて指定する

(2019)


OPTION (LOOP JOIN)などのクエリヒントは対象テーブルを指定できず、クエリの全結合が対象となる

3テーブル以上で個別に制御する場合は、ANSI-style join hintを使用する