問い合わせ変換ヒント句 - HTN20190109の日記

MySQL

(8.0.22)
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

①OR条件をUNION ALLに変換する/しない
ヒント句なし
インデックスレベルのマージはINDEX_MERGE/NO_INDEX_MERGE

②ビューをマージする/しない
MERGE/NO_MERGE

③副問合せのネストを解除する/しない
SEMIJOIN/NO_SEMIJOIN

④述語をプッシュする/しない
DERIVED_CONDITION_PUSHDOWN/NO_DERIVED_CONDITION_PUSHDOWN

-- テストデータ作成

drop table tab1;
drop table tab2;
drop table tab3;
drop table tab4;

create table tab1(col1 int, col2 int, col3 int);
create table tab2(col1 int, col2 int, col3 int);
create table tab3(col1 int, col2 int, col3 int);
create table tab4(col1 int, col2 int, col3 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);
alter table tab3 add constraint tab3pk primary key(col1);
alter table tab4 add constraint tab4pk primary key(col1);

drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 100000 do
insert into tab1 values(i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
insert into tab2 values(i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
insert into tab3 values(i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
insert into tab4 values(i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
set i = i + 1;
end while;
end
//
delimiter ;

start transaction;
call proc1();
commit;

select count(*) from tab1;
select count(*) from tab2;
select count(*) from tab3;
select count(*) from tab4;

drop index ind11 on tab1;
drop index ind12 on tab1;
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);

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

-- ①

explain
select /*+ INDEX_MERGE(tab1 primary,ind11,ind12) */
col1,col2,col3
from tab1
where col1 = 99 or col2 = 99 or col3 = 99
;

explain
select /*+ NO_INDEX_MERGE(tab1 primary,ind11,ind12) */
col1,col2,col3
from tab1
where col1 = 99 or col2 = 99 or col3 = 99
;

ヒント句なしの場合、インデックスレベルのマージとなる

-- ②

explain
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select /*+ MERGE(t0) */
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col1 = 123
;

※ビューにgroup byがあるとマージできない

explain
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
)
select /*+ MERGE(t0) */
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col1 = 123
;

explain
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
)
select /*+ NO_MERGE(t0) */
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col1 = 123
;

ヒント句なしの場合、ビューをマージする

-- ③

explain
select
t1.col1,t1.col2,t1.col3
from tab1 t1
where exists ( select /*+ SEMIJOIN() */ 1 from tab2 t2 where t2.col3 = t1.col3)
;

explain
select
t1.col1,t1.col2,t1.col3
from tab1 t1
where exists ( select /*+ NO_SEMIJOIN() */ 1 from tab2 t2 where t2.col3 = t1.col3)
;

ヒント句なしの場合、ネスト解除となる

-- ④

explain
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select /*+ DERIVED_CONDITION_PUSHDOWN() */
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t0.col4 = 1
;

explain
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select /*+ NO_DERIVED_CONDITION_PUSHDOWN() */
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t0.col4 = 1
;

ヒント句なしの場合、述語をプッシュする

Oracle

https://blogs.oracle.com/otnjp/post/tsushima-hakushi-60
(19c)

①OR条件をUNION ALLに変換する/しない
USE_CONCAT/NO_EXPAND
→CONCATENATIONが使用される

OR_EXPAND/NO_OR_EXPAND
→UNION ALLが使用される

②ビューをマージする/しない
MERGE/NO_MERGE

③副問合せのネストを解除する/しない
UNNEST/NO_UNNEST

④述語をプッシュする/しない
PUSH_PRED/NO_PUSH_PRED

-- テストデータ作成

drop table tab1 purge;
drop table tab2 purge;
drop table tab3 purge;
drop table tab4 purge;

create table tab1(col1 int, col2 int, col3 int);
create table tab2(col1 int, col2 int, col3 int);
create table tab3(col1 int, col2 int, col3 int);
create table tab4(col1 int, col2 int, col3 int);

declare
begin
for i in 1..100000 loop
insert into tab1 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 101)) );
insert into tab2 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 101)) );
insert into tab3 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 101)) );
insert into tab4 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 101)) );
commit;
end loop;
end;
/

select count(*) from tab1;
select count(*) from tab2;
select count(*) from tab3;
select count(*) from tab4;

drop index ind11;
drop index ind12;
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);

exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');
exec dbms_stats.gather_table_stats('TEST','TAB3');
exec dbms_stats.gather_table_stats('TEST','TAB4');

-- ①

explain plan for
select /*+ USE_CONCAT INDEX(tab1 ind11 ind12) */
col1,col2,col3
from tab1
where col1 = 99 or col2 = 99 or col3 = 99
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

explain plan for
select /*+ NO_EXPAND INDEX(tab1 ind11 ind12) */
col1,col2,col3
from tab1
where col1 = 99 or col2 = 99 or col3 = 99
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

ヒント句なしの場合、インデックスレベルのビットマップORとなる

explain plan for
select /*+ OR_EXPAND INDEX(tab1 tab1pk ind11 ind12) */
col1,col2,col3
from tab1
where col1 = 99 or col2 = 99 or col3 = 99
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

explain plan for
select /*+ NO_OR_EXPAND */
col1,col2,col3
from tab1
where col1 = 99 or col2 = 99 or col3 = 99
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

-- ②

explain plan for
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col1 = 123
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain plan for
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select /*+ NO_MERGE(t0) */
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col1 = 123
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

ヒント句なしの場合、ビューをマージしない

-- ③

explain plan for
select
t1.col1,t1.col2,t1.col3
from tab1 t1
where exists ( select /*+ UNNEST */ 1 from tab2 t2 where t2.col3 = t1.col3)
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain plan for
select
t1.col1,t1.col2,t1.col3
from tab1 t1
where exists ( select /*+ NO_UNNEST */ 1 from tab2 t2 where t2.col3 = t1.col3)
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

ヒント句なしの場合、ネストを解除(SEMIJOIN)となる

-- ④

explain plan for
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select /*+ NO_MERGE(t0) PUSH_PRED(t0) */
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col2 = 1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain plan for
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select /*+ NO_MERGE(t0) NO_PUSH_PRED(t0) */
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col2 = 1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

ヒント句なしの場合、述語(結合条件)をプッシュする

PostgreSQL

(13)

①OR条件をUNION ALLに変換する/しない
ヒント句なし

②ビューをマージする/しない
ヒント句なし

③副問合せのネストを解除する/しない
ヒント句なし

④述語をプッシュする/しない
ヒント句なし

-- テストデータ作成

drop table tab1;
drop table tab2;
drop table tab3;
drop table tab4;

create table tab1(col1 int, col2 int, col3 int);
create table tab2(col1 int, col2 int, col3 int);
create table tab3(col1 int, col2 int, col3 int);
create table tab4(col1 int, col2 int, col3 int);

do $$
declare
i int;
begin
i = 1;
while i <= 100000 loop
insert into tab1 values(i,floor(random() * 10000)+1,floor(random() * 100)+1);
insert into tab2 values(i,floor(random() * 10000)+1,floor(random() * 100)+1);
insert into tab3 values(i,floor(random() * 10000)+1,floor(random() * 100)+1);
insert into tab4 values(i,floor(random() * 10000)+1,floor(random() * 100)+1);
i = i + 1;
end loop;
end
$$
;

select count(*) from tab1;
select count(*) from tab2;
select count(*) from tab3;
select count(*) from tab4;

drop index ind11;
drop index ind12;
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);

analyze tab1;
analyze tab2;
analyze tab3;
analyze tab4;

-- ①

explain analyze
select
col1,col2,col3
from tab1
where col1 = 99 or col2 = 99 or col3 = 99
;

インデックスレベルのビットマップORとなる

-- ②

explain analyze
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col1 = 123
;

ビューをマージしない

-- ③

explain analyze
select
t1.col1,t1.col2,t1.col3
from tab1 t1
where exists ( select 1 from tab2 t2 where t2.col3 = t1.col3)
;

ネスト解除となる

-- ④

述語をプッシュする

SQL Server

(2019)
https://docs.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15

①OR条件をUNION ALLに変換する/しない
ヒント句なし

②ビューをマージする/しない
ヒント句なし

③副問合せのネストを解除する/しない
ヒント句なし

④述語をプッシュする/しない
ヒント句なし

-- テストデータ作成

drop table tab1;
drop table tab2;
drop table tab3;
drop table tab4;

create table tab1(col1 int not null, col2 int, col3 int);
create table tab2(col1 int not null, col2 int, col3 int);
create table tab3(col1 int not null, col2 int, col3 int);
create table tab4(col1 int not null, col2 int, col3 int);

set nocount on
declare @i int;
set @i = 1;

while (@i <= 100000)
begin
insert into tab1 values(@i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
insert into tab2 values(@i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
insert into tab3 values(@i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
insert into tab4 values(@i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
set @i = @i + 1;
end

select count(*) from tab1;
select count(*) from tab2;
select count(*) from tab3;
select count(*) from tab4;

drop index ind11 on tab1;
drop index ind12 on tab1;
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);

update statistics tab1;
update statistics tab2;
update statistics tab3;
update statistics tab4;

-- ①

select
col1,col2,col3
from tab1 with (index(tab1pk,ind11,ind12))
where col1 = 99 or col2 = 99 or col3 = 99
;

各インデックスを使用したハッシュ結合となる

-- ②

with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col1 = 123
;

ビューをマージしない

-- ③

select
t1.col1,t1.col2,t1.col3
from tab1 t1
where exists ( select 1 from tab2 t2 where t2.col3 = t1.col3)
;

ネスト解除となる

-- ④

述語をプッシュする