ヒントレポート

• MySQL

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0117
(8.0)

drop table tab1;
drop table tab2;

create table tab1(col1 int);
create table tab2(col1 int);

drop procedure proc1;

delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;

while i <= 100000 do
insert into tab1 values(i);
set i = i+1;
end while;
end
//
delimiter ;

call proc1();

SET @num=0;
insert into tab2 select @num:=@num+1 from tab1 limit 100;

analyze table tab1;
analyze table tab2;

explain
select
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
show warnings;

explain
select /*+ JOIN_ORDER(A, B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
show warnings;

explain
select /*+ J0IN_ORDER(A, B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
show warnings;

• Oracle

(19C)

https://qiita.com/plusultra/items/f000dce82266747fce81
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-72-5572834-ja.html

-- データ準備
drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 int);
create table tab2(col1 int);

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i);
commit;
end loop;
end;
/

insert into tab2
with t1(col1) as(
select 1 from dual
union all
select col1+1
from t1
where col1+1 <= 100
)
select col1 from t1
;
commit;

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

-- 動作確認

explain plan for
select /*+ use_hash(A B) FULL(A) FULL(B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;

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

explain plan for
select /*+ use_hash(A B) FULL(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;

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

• PostgreSQL

(9.4)

-- 設定
show pg_hint_plan.debug_print;
set pg_hint_plan.debug_print = on;
show pg_hint_plan.debug_print;

cd /var/lib/pgsql/9.4/data/pg_log
tail -f postgresql-Sat.log

-- データ準備

drop table tab1;
drop table tab2;

create table tab1(col1 int);
create table tab2(col1 int);

do $$
declare
begin
for i in 1..100000 loop
insert into tab1 values(i);
end loop;
end
$$
;

insert into tab2 select generate_series(1,100);

analyze tab1;
analyze tab2;

-- 動作確認

explain analyze
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;

explain analyze
/*+ NestLoop(a b) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;

→
used hint:
NestLoop(a b)
not used hint:
duplication hint:
error hint:

explain analyze
/*+ NestL00p(a b) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;

→
何も表示されない

explain analyze
/*+ NestLoop(a c) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;

→
used hint:
not used hint:
NestLoop(a c)
duplication hint:
error hint:

• SQL Server

(2014)

構文の一部のため、無効であればSQL自体がエラーとなる