(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() * 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 /*+ JOIN_PREFIX(t1 , t6,t5) */ count(*) from tab1 t1
where exists ( select 1 from
(
select /* QB_NAME(qb1) */ /* JOIN_PREFIX(t22 , t21) */ 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) */ /* JOIN_PREFIX(t32 , t31) */ 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) */ 1 from tab1 t4 where t1.col1 = t4.col2)
and exists ( select /* QB_NAME(qb4) */ /* JOIN_PREFIX(t6 , t5) */ 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;
-- 問合せブロック付きヒント
/*+ JOIN_PREFIX(t32@qb2 , t1 ) */ → OK
/*+ JOIN_PREFIX(t22@qb1 , t1 ) */ → Unresolved
/*+ JOIN_PREFIX(t4@qb3 , t1 ) */ → OK
/*+ JOIN_PREFIX(t5@qb4 , t1 ) */ → OK
グループ化したサブクエリ内のテーブルはleadingヒント句に指定不可能
-- サブクエリ名を指定
/*+ JOIN_PREFIX(t3 ,t1) */ → Unresolved
/*+ JOIN_PREFIX(t1 ,t2) */ → OK
グループ化していないサブクエリはleadingヒント句に指定不可能
グループ化したサブクエリはleadingヒント句に指定可能
-- サブクエリ内部名を本体に指定
/*+ JOIN_PREFIX(t1 , t32) */ → OK
/*+ JOIN_PREFIX(t1 , t22) */ → Unresolved
/*+ JOIN_PREFIX(t1 , t4) */ → OK
/*+ JOIN_PREFIX(t1 , t6) */ → OK
-- サブクエリ内部名をサブクエリに指定
/*+ JOIN_PREFIX(t32 , t31) */ → 警告はでないが実行計画変化なし
/*+ JOIN_PREFIX(t22 , t21) */ → OK
/*+ JOIN_PREFIX(t6 , t5) */ → 警告はでないが実行計画変化なし
(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 /* LEADING(T1 T6) */ count(*) from tab1 t1
where exists ( select 1 from
(
select /* QB_NAME(qb1) */ /* LEADING(T22 T21) */ 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) */ /* LEADING(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) */ 1 from tab1 t4 where t1.col1 = t4.col2)
and exists ( select /* QB_NAME(qb4) */ /*+ LEADING(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 問合せブロック付きヒント(システム生成)
/*+ LEADING(T32@SEL$7 T1@SEL$2 ) */ → Unused
/*+ LEADING(T22@SEL$4 T1@SEL$2 ) */ → Unused
/*+ LEADING(T4@SEL$9 T1@SEL$2 ) */ → OK
/*+ LEADING(T5@SEL$1 T1@SEL$2 ) */ → Unused
シンプルなサブクエリはleadingヒント句指定可能
--1.2 問合せブロック付きヒント(ユーザ指定)
/*+ LEADING(T32@qb2 T1 ) */ → Unused
/*+ LEADING(T22@qb1 T1 ) */ → Unused
/*+ LEADING(T4@qb3 T1 ) */ → OK
/*+ LEADING(T5@qb4 T1 ) */ → Unused
--1.3 グローバル・ヒント
/*+ LEADING(T3.T32 T1 ) */ →グローバルヒントが認識されない
/*+ LEADING(T3.T32) */ →グローバルヒントが認識されない
--1.4 サブクエリ名を指定
/*+ LEADING(T1 T3) */ → Unused
/*+ LEADING(T1 T2) */ → OK
グループ化していないサブクエリはleadingヒント句に指定不可能
グループ化したサブクエリはleadingヒント句に指定可能
--1.5 サブクエリ内部名を本体に指定
/*+ LEADING(T1 T32) */ → Unused
/*+ LEADING(T1 T22) */ → Unused
/*+ LEADING(T1 T4) */ → OK
/*+ LEADING(T1 T6) */ → Unused
--1.6 サブクエリ内部名をサブクエリに指定
/*+ LEADING(T31 T32) */ → OK
/*+ LEADING(T22 T21) */ → OK
/*+ LEADING(T5 T6) */ → OK
--1.7 leadingヒントを本体とインラインビューに両方指定した場合の挙動
/*+ LEADING(T2) */ と /*+ LEADING(T22 T21) */ の組み合わせは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() * 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;
/*+ Leading(t1 t32) */
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)
;
-- サブクエリ名を指定
/*+ Leading(t1 t3) */ → not used hint
/*+ Leading(t1 t2) */ → used hint
グループ化していないサブクエリはleadingヒント句に指定不可能
グループ化したサブクエリはleadingヒント句に指定可能
-- サブクエリ内部名を本体に指定
/*+ Leading(t1 t32) */ → used hint
/*+ Leading(t1 t22) */ → not used hint
/*+ Leading(t1 t4) */ → used hint
/*+ Leading(t1 t6) */ → used hint
(2019)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 bigint not null
,col2 bigint
);
drop table tab2;
create table tab2(
col1 bigint not null
,col2 bigint
);
drop table tab3;
create table tab3(
col1 bigint not null
,col2 bigint
);
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
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
);
set @i = @i + 1;
end
commit;
select count(*) from tab1;
select top 20 * from tab1 order by newid();
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);
select count(*) from tab2;
select top 20 * from tab2 order by newid();
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);
select count(*) from tab3;
select top 20 * from tab3 order by newid();
alter table tab3 add constraint tab3pk primary key (col1);
create index ind32 on tab3(col2);
set statistics time on
update statistics tab1;
update statistics tab2;
update statistics tab3;
--2. 動作確認
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)
OPTION (FORCE ORDER)
;
FORCE ORDERクエリヒントしかないので、SQL文組み換えと合わせて適用する
※サブクエリについてもFORCE ORDERは有効