(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 |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9994K| 228M| | 653 (6)| 00:00:01 |
| 1 | MERGE JOIN | | 9994K| 228M| | 653 (6)| 00:00:01 |
| 2 | SORT JOIN | | 100 | 900 | | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TAB1 | 100 | 900 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 100K| 1464K| 5512K| 617 (1)| 00:00:01 |
| 5 | 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 |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4950 | 116K| | 621 (1)| 00:00:01 |
| 1 | MERGE JOIN | | 4950 | 116K| | 621 (1)| 00:00:01 |
| 2 | SORT JOIN | | 100 | 900 | | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TAB2 | 100 | 900 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 100K| 1464K| 5512K| 617 (1)| 00:00:01 |
| 5 | 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);
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.col11 = t2.col21
and t1.col13 < t2.col23
;
------------------------------------------------------------------------------------
Merge Join (cost=5.61..10.73 rows=33 width=24)
Merge Cond: (t2.col21 = t1.col11)
Join Filter: (t1.col13 < t2.col23)
-> Index Scan using cons2 on tab2 t2 (cost=0.29..3148.29 rows=100000 width=12)
-> Sort (cost=5.32..5.57 rows=100 width=12)
Sort Key: t1.col11
-> 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);
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.col11 = t2.col21
and t1.col13 < t2.col23
;
------------------------------------------------------------------------------------
Merge Join (cost=5.61..10.69 rows=33 width=24)
Merge Cond: (t1.col11 = t2.col21)
Join Filter: (t1.col13 < t2.col23)
-> Index Scan using cons1 on tab1 t1 (cost=0.29..3148.29 rows=100000 width=12)
-> Sort (cost=5.32..5.57 rows=100 width=12)
Sort Key: t2.col21
-> Seq Scan on tab2 t2 (cost=0.00..2.00 rows=100 width=12)
(2014)
非等価結合でmerge joinを選択できない模様
※OPTION (MERGE JOIN)ヒント句指定でエラー発生
