ビューとleadingヒント句

• MySQL

(8.0.26)

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

create or replace view view11 as
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
;

create or replace view view21 as
select  t31.col2 from tab2 t31 inner join tab3 t32
on t31.col1 = t32.col1
;

--2. 動作確認

explain 
select /*+ JOIN_PREFIX(t1, t21)   */      count(*) from tab1 t1
inner join view11 t2
on t1.col2 = t2.col2
inner join view21  t3
on t1.col2 = t3.col2
;
show warnings;

-- ビュー名を指定

/*+ JOIN_PREFIX(t1, t3)   */  → Unresolved
/*+ JOIN_PREFIX(t1, t2)   */  → OK

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

/*+ JOIN_PREFIX(t1, t31)   */   → OK
/*+ JOIN_PREFIX(t1, t21)   */   → Unresolved

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

create or replace view view11 as
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
;

create or replace view view12 as
select  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
;

create or replace view view21 as
select  t31.col2 from tab2 t31 inner join tab3 t32
on t31.col1 = t32.col1
;

create or replace view view22 as
select  t31.col2 from tab2 t31 , tab3 t32
where t31.col1 = t32.col1
;

--2. 動作確認

[1]ANSI準拠の結合文

explain plan for
select  /*+ LEADING(T22 T1 T21) */   count(*) from tab1 t1
inner join
view11 t2
on t1.col2 = t2.col2
inner join
view21 t3
on t1.col2 = t3.col2
;

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

--1.1 問合せブロック付きヒント(システム生成)
/*+ LEADING(T32@SEL$5 T1@SEL$1 T31@SEL$5 ) */   →OK
/*+ LEADING(T22@SEL$2 T1@SEL$1 T21@SEL$2 ) */    → Unused
 

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

--1.2 グローバル・ヒント
/*+ LEADING(T3.T32 T1 T3.T31) */   →グローバルヒントが認識されない
/*+ LEADING(T3.T32) */             →グローバルヒントが認識されない ★

Oracle Databaseは、複数の問合せブロックを参照するグローバル・ヒントを無視します。

--1.3 ビュー名を指定

/*+ LEADING(T3) */   →Unused
/*+ LEADING(T2) */    →OK

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

--1.4 ビュー内部名を本体に指定

/*+ LEADING(T32 T1 T31) */    →OK
/*+ LEADING(T22 T1 T21) */   → Unused

[2]Oracle固有の結合文

explain plan for
select   /*+ LEADING(T22 T1 T21) */      count(*) from tab1 t1
,view12 t2
,view22 t3
where t1.col2 = t2.col2
and t1.col2 = t3.col2
;

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

--2.1 問合せブロック付きヒント(システム生成)
/*+ LEADING(T32@SEL$3 T1@SEL$1 T31@SEL$3 ) */   →OK
/*+ LEADING(T22@SEL$2 T1@SEL$1 T21@SEL$2 ) */  → Unused
 
グループ化していないビュー内のテーブルはleadingヒント句に指定可能
グループ化したビュー内のテーブルはleadingヒント句に指定不可能

--2.2 グローバル・ヒント
/*+ LEADING(T3.T32 T1 T3.T31) */   →グローバルヒントが認識されない
/*+ LEADING(T3.T32) */             → OK

Oracle Databaseは、複数の問合せブロックを参照するグローバル・ヒントを無視します。

--2.3 ビュー名を指定
/*+ LEADING(T3) */     →Unused
/*+ LEADING(T2) */     →OK

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

--2.4 ビュー内部名を本体に指定
/*+ LEADING(T32 T1 T31) */      →OK
/*+ LEADING(T22 T1 T21) */     → Unused

• PostgreSQL

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

create or replace view view11 as
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
;

create or replace view view21 as
select  t31.col2 from tab2 t31 inner join tab3 t32
on t31.col1 = t32.col1
;

--2. 動作確認

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

/*+ Leading(t1 t21) */
explain 
select  count(*) from tab1 t1
inner join
view11 t2
on t1.col2 = t2.col2
inner join
view21 t3
on t1.col2 = t3.col2
;

-- ビュー名を指定
/*+ Leading(t1 t3) */    → not used hint
/*+ Leading(t1 t2) */    → used hint 

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

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

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

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

• SQL Server

(2019)
調査観点なし