(8.0.18)
※HASH_JOIN, NO_HASH_JOINヒント句が8.0.18でしか動作しないため、8.0.18を使用
前提：
テーブル1,2にインデックスなし
テーブル1の件数=1000000
テーブル2の件数=100
テーブル1,2の結合後の件数=1000000
drop table tab1;
drop table tab2;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
declare j int;
set i = 1;
set j = 1;
while i <= 1000000 do
insert into tab1 values(j,i);
set i = i+1;
set j = j+1;
if j > 100 then
set j = 1;
end if;
end while;
end
//
delimiter ;
call proc1();
SET @num=0;
insert into tab2 select @num:=@num+1,@num from tab1 limit 100;
analyze table tab1;
analyze table tab2;
explain analyze
select
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain
select /*+ JOIN_ORDER(A, B) NO_HASH_JOIN(A, B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain
select /*+ JOIN_ORDER(B, A) NO_HASH_JOIN(A, B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain analyze
select /*+ JOIN_ORDER(A, B) HASH_JOIN(A, B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain analyze
select /*+ JOIN_ORDER(B, A) HASH_JOIN(A, B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
確認結果：
NLは結合順の影響ほぼなし
HJは件数が少ないテーブルがbuild表(実行計画で2番目のテーブル)となるほうが有利
(12cR1)
前提：
テーブル1,2にインデックスなし
テーブル1の件数=1000000
テーブル2の件数=100
テーブル1,2の結合後の件数=1000000
drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);
declare
j integer;
k integer;
begin
j := 1;
k := 1;
for i in 1..1000000 loop
insert into tab1 values(j,i);
commit;
j := j+1;
if j > 100 then
j := 1;
end if;
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,col1 from t1
;
commit;
exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');
explain plan for
select
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
select /*+ use_nl(A B) leading(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
select /*+ use_nl(A B) leading(B A) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
select /*+ use_hash(A B) leading(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
select /*+ use_hash(A B) leading(B A) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
select /*+ use_merge(A B) leading(A B) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
select /*+ use_merge(A B) leading(B A) */
count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
確認結果：
NLは件数が少ないテーブルが駆動表(実行計画で1番目のテーブル)となるほうが有利
HJは件数が少ないテーブルがbuild表(実行計画で1番目のテーブル)となるほうが有利
MJは件数が少ないテーブルが駆動表(実行計画で1番目のテーブル)となるほうが有利
(9.4)
前提：
テーブル1,2にインデックスなし
テーブル1の件数=1000000
テーブル2の件数=100
テーブル1,2の結合後の件数=1000000
drop table tab1;
drop table tab2;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);
do $$
declare
j integer;
k integer;
begin
j := 1;
k := 1;
for i in 1..1000000 loop
insert into tab1 values(j,i);
j := j+1;
if j > 100 then
j := 1;
end if;
end loop;
end
$$
;
insert into tab2 select generate_series(1,100),generate_series(1,100);
analyze tab1;
analyze tab2;
explain ( buffers ,analyze )
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain ( buffers ,analyze )
/*+ NestLoop(a b) Leading((a b)) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain ( buffers ,analyze )
/*+ NestLoop(a b) Leading((b a)) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain ( buffers ,analyze )
/*+ Hashjoin(a b) Leading((a b)) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain ( buffers ,analyze )
/*+ Hashjoin(a b) Leading((b a)) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain ( buffers ,analyze )
/*+ Mergejoin(a b) Leading((a b)) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
explain ( buffers ,analyze )
/*+ Mergejoin(a b) Leading((b a)) */
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
確認結果：
NLは件数が多いテーブルが駆動表(実行計画で1番目のテーブル)となるほうが有利
→内部表の結果が共有バッファに入りきらずにこのようになっている模様
HJは件数が少ないテーブルがbuild表(実行計画で2番目のテーブル)となるほうが有利
MJは結合順の影響なし
(2014)
前提：
テーブル1,2にインデックスなし
テーブル1の件数=1000000
テーブル2の件数=100
テーブル1,2の結合後の件数=1000000
drop table tab1;
drop table tab2;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);
declare @i int;
declare @j int;
set @i= 1;
set @j= 1;
while (@i <= 1000000)
begin
insert into tab1 values (@j,@i);
set @i = @i + 1;
set @j = @j + 1;
if @j > 100
begin
set @j = 1;
end
end
go
with t1(col1) as(
select 1
union all
select col1+1
from t1
where col1+1 <= 100
)
insert into tab2 select col1,col1 from t1
;
update statistics tab1;
update statistics tab2;
set showplan_text on
go
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
;
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
OPTION (LOOP JOIN,FORCE ORDER)
;
select count(*)
from tab2 B inner join tab1 A
on A.col1 = B.col1
OPTION (LOOP JOIN,FORCE ORDER)
;
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
OPTION (HASH JOIN,FORCE ORDER)
;
select count(*)
from tab2 B inner join tab1 A
on A.col1 = B.col1
OPTION (HASH JOIN,FORCE ORDER)
;
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
OPTION (MERGE JOIN,FORCE ORDER)
;
select count(*)
from tab2 B inner join tab1 A
on A.col1 = B.col1
OPTION (MERGE JOIN,FORCE ORDER)
;
確認結果：
NLは件数が多いテーブルが駆動表(実行計画で1番目のテーブル)となるほうがやや有利
HJは件数が少ないテーブルがbuild表(実行計画で1番目のテーブル)となるほうが有利
MJは件数が多いテーブルが駆動表(実行計画で1番目のテーブル)となるほうがやや有利
