hash join

• MySQL

(5.6)
サポートしていない

• Oracle

(12cR1)

--確認事項
(絞り込み後)件数が少ないテーブルが外部表となること

外部表→ビルド表
内部表→プローブ表

--データ準備

drop table tab1;
drop table tab2;

create table tab1(col11 int,col12 int,col13 int);
create table tab2(col21 int,col22 int,col23 int);

create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);

alter table tab1 add constraint cons1 primary key(col11) using index ind11;
alter table tab2 add constraint cons2 primary key(col21,col22) using index ind21;

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

declare
begin
for i in 1..100000 loop
insert into tab2 values(i,i,i);
commit;
end loop;
end;
/

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

--実行計画確認

explain plan for
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col13 = t2.col23
;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2400 | 106 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 100 | 2400 | 106 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB1 | 100 | 900 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TAB2 | 100K| 1464K| 103 (1)| 00:00:01 |
---------------------------------------------------------------------------

--データ準備

drop table tab1;
drop table tab2;

create table tab1(col11 int,col12 int,col13 int);
create table tab2(col21 int,col22 int,col23 int);

create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);

alter table tab1 add constraint cons1 primary key(col11) using index ind11;
alter table tab2 add constraint cons2 primary key(col21,col22) using index ind21;

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

declare
begin
for i in 1..100 loop
insert into tab2 values(i,i,i);
commit;
end loop;
end;
/

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

--実行計画確認

explain plan for
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col13 = t2.col23
;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2400 | 106 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 100 | 2400 | 106 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB2 | 100 | 900 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TAB1 | 100K| 1464K| 103 (1)| 00:00:01 |
---------------------------------------------------------------------------

• PostgreSQL

(9.4)

--確認事項
(絞り込み後)件数が少ないテーブルが内部表となること

※Oracleと逆

外部表→プローブ表
内部表→ビルド表

--データ準備

drop table tab1;
drop table tab2;

create table tab1(col11 int,col12 int,col13 int);
create table tab2(col21 int,col22 int,col23 int);

create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);

alter table tab1 add constraint cons1 primary key using index ind11;
alter table tab2 add constraint cons2 primary key using index ind21;

insert into tab1 select generate_series(1,100),generate_series(1,100),generate_series(1,100);
insert into tab2 select generate_series(1,100000),generate_series(1,100000),generate_series(1,100000);

analyze tab1;
analyze tab2;

--実行計画確認

explain
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col13 = t2.col23
;

----------------------------------------------------------------------
Hash Join (cost=3.25..1920.25 rows=100 width=24)
Hash Cond: (t2.col23 = t1.col13)
-> Seq Scan on tab2 t2 (cost=0.00..1541.00 rows=100000 width=12)
-> Hash (cost=2.00..2.00 rows=100 width=12)
-> Seq Scan on tab1 t1 (cost=0.00..2.00 rows=100 width=12)

--データ準備

drop table tab1;
drop table tab2;

create table tab1(col11 int,col12 int,col13 int);
create table tab2(col21 int,col22 int,col23 int);

create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);

alter table tab1 add constraint cons1 primary key using index ind11;
alter table tab2 add constraint cons2 primary key using index ind21;

insert into tab1 select generate_series(1,100000),generate_series(1,100000),generate_series(1,100000);
insert into tab2 select generate_series(1,100),generate_series(1,100),generate_series(1,100);

analyze tab1;
analyze tab2;

--実行計画確認

explain
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col13 = t2.col23
;

----------------------------------------------------------------------
Hash Join (cost=3.25..1920.25 rows=100 width=24)
Hash Cond: (t1.col13 = t2.col23)
-> Seq Scan on tab1 t1 (cost=0.00..1541.00 rows=100000 width=12)
-> Hash (cost=2.00..2.00 rows=100 width=12)
-> Seq Scan on tab2 t2 (cost=0.00..2.00 rows=100 width=12)

• SQL Server

(2014)

--確認事項
(絞り込み後)件数が少ないテーブルが外部表となること

外部表→ビルド表
内部表→プローブ表

--データ準備

drop table tab1;
drop table tab2;
go

create table tab1(col11 int not null,col12 int,col13 int);
create table tab2(col21 int not null,col22 int not null, col23 int);

create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);

alter table tab1 add constraint cons1 primary key(col11);
alter table tab2 add constraint cons2 primary key(col21,col22);

DECLARE @counter int;
SET @counter = 1;

WHILE (@counter <= 100)
BEGIN
INSERT INTO tab1 VALUES (@counter,@counter,@counter);
SET @counter = @counter + 1;
END

DECLARE @counter int;
SET @counter = 1;

WHILE (@counter <= 100000)
BEGIN
INSERT INTO tab2 VALUES (@counter,@counter,@counter);
SET @counter = @counter + 1;
END

UPDATE STATISTICS tab1;
UPDATE STATISTICS tab2;

--実行計画確認
set showplan_text on
go
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col13 = t2.col23
;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([t1].[col13])=([t2].[col23]), RESIDUAL:([test].[dbo].[tab2].[col23] as [t2].[col23]=[test].[dbo].[tab1].[col13] as [t1].[col13]))
|--Clustered Index Scan(OBJECT:([test].[dbo].[tab1].[cons1] AS [t1]))
|--Clustered Index Scan(OBJECT:([test].[dbo].[tab2].[cons2] AS [t2]))

--データ準備

drop table tab1;
drop table tab2;
go

create table tab1(col11 int not null,col12 int,col13 int);
create table tab2(col21 int not null,col22 int not null, col23 int);

create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);

alter table tab1 add constraint cons1 primary key(col11);
alter table tab2 add constraint cons2 primary key(col21,col22);

DECLARE @counter int;
SET @counter = 1;

WHILE (@counter <= 100000)
BEGIN
INSERT INTO tab1 VALUES (@counter,@counter,@counter);
SET @counter = @counter + 1;
END

DECLARE @counter int;
SET @counter = 1;

WHILE (@counter <= 100)
BEGIN
INSERT INTO tab2 VALUES (@counter,@counter,@counter);
SET @counter = @counter + 1;
END

UPDATE STATISTICS tab1;
UPDATE STATISTICS tab2;

--実行計画確認
set showplan_text on
go
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col13 = t2.col23
;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([t2].[col23])=([t1].[col13]), RESIDUAL:([test].[dbo].[tab2].[col23] as [t2].[col23]=[test].[dbo].[tab1].[col13] as [t1].[col13]))
|--Clustered Index Scan(OBJECT:([test].[dbo].[tab2].[cons2] AS [t2]))
|--Clustered Index Scan(OBJECT:([test].[dbo].[tab1].[cons1] AS [t1]))