(5.6)
サポートしていない
(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 |
---------------------------------------------------------------------------
(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)
(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]))
