外部結合→内部結合の自動変換

いずれのDBも内部結合への自動変換をサポートしている模様

(8.0.29)

drop table tab1;
create table tab1(
    col1 bigint primary key
   ,col2 bigint
   );

drop table tab2;
create table tab2(
    col1 bigint primary key
   ,col2 bigint
   );

 

drop procedure proc1;

delimiter //
create procedure proc1(in x int)
begin
  declare i int;
  set i = 0;
  start transaction;
  while i < x do
    set i = i + 1;
    insert into tab1 values(
     i
    ,i
    );
    insert into tab2 values(
     i
    ,i
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);

analyze table tab1;
analyze table tab2;

 


explain format=tree
select 
  t1.col1 col11
 ,t1.col2 col12
 ,t2.col2 col22
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 123
;


explain format=tree
select 
  t1.col1 col11
 ,t1.col2 col12
 ,t2.col2 col22
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col1
where t2.col2 = 123
;


外部結合されるテーブルに非ヌル条件がある場合、内部結合となる

(19c)

drop table tab1 purge;
create table tab1(
    col1 int constraint tab1pk primary key
   ,col2 int
  );

drop table tab2 purge;
create table tab2(
    col1 int constraint tab2pk primary key
   ,col2 int
   );


declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
    i
   ,i
   );
  insert into tab2 values(
    i
   ,i
   );

end loop;
end;
/

commit;
set time on
set timing on

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


explain plan for
select 
  t1.col1 col11
 ,t1.col2 col12
 ,t2.col2 col22
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 123
;

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


explain plan for
select 
  t1.col1 col11
 ,t1.col2 col12
 ,t2.col2 col22
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col1
where t2.col2 = 123
;

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


外部結合されるテーブルに非ヌル条件がある場合、内部結合となる

 

 

(14)

drop table tab1 cascade;
create table tab1(
    col1 bigint constraint tab1pk primary key
   ,col2 bigint
  );
drop table tab2 cascade;
create table tab2(
    col1 bigint constraint tab2pk primary key
   ,col2 bigint
   );

 


start transaction;
insert into tab1 select
   g
  ,g
from generate_series(1,1000000) g;

commit;

start transaction;
insert into tab2 select
   g
  ,g
from generate_series(1,1000000) g;

commit;


\timing 1
\pset pager 0

 

analyze tab1;
analyze tab2;


explain analyze
select 
  t1.col1 col11
 ,t1.col2 col12
 ,t2.col2 col22
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 123
;

explain analyze
select 
  t1.col1 col11
 ,t1.col2 col12
 ,t2.col2 col22
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col1
where t2.col2 = 123
;


外部結合されるテーブルに非ヌル条件がある場合、内部結合となる

(2019)


drop table tab1;
create table tab1(
    col1 bigint not null constraint tab1pk primary key
   ,col2 bigint
   );

drop table tab2;
create table tab2(
    col1 bigint not null  constraint tab2pk primary key
   ,col2 bigint
   );


set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
   insert into tab1 values(
     @i
    ,@i
    );
   insert into tab2 values(
     @i
    ,@i
    );
 set @i = @i + 1;
end
commit;


update statistics tab1;
update statistics tab2;

set statistics time on


set showplan_all on


select 
  t1.col1 col11
 ,t1.col2 col12
 ,t2.col2 col22
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 123
;


select 
  t1.col1 col11
 ,t1.col2 col12
 ,t2.col2 col22
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col1
where t2.col2 = 123
;

 

外部結合されるテーブルに非ヌル条件がある場合、内部結合となる