結合条件の推移律

Oracleのみ結合条件の推移律未対応

(8.0.26)

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

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

drop table tab3;
create table tab3(
    col1 bigint 
   ,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
    ,floor(rand() * 100000)+1
    );
    insert into tab2 values(
     i
    ,floor(rand() * 100000)+1
    );
    insert into tab3 values(
     i
    ,floor(rand() * 100000)+1
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);

analyze table tab1;
analyze table tab2;
analyze table tab3;

explain analyze
select  /*+ JOIN_PREFIX(t2 ,t3 ,t1) */  count(*) 
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;

show warnings;

→ Inner hash join (t3.col1 = t2.col1)となり、結合条件の推移律はある

(19c)

drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 int
  );

drop table tab2 purge;
create table tab2(
    col1 int 
   ,col2 int
   );

drop table tab3 purge;
create table tab3(
    col1 int 
   ,col2 int
   );

declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
    i
   ,floor(dbms_random.value(1, 100001) )
   );
  
  insert into tab2 values(
    i
   ,floor(dbms_random.value(1, 100001) )
   );
  insert into tab3 values(
    i
   ,floor(dbms_random.value(1, 100001) )
   );
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');
exec dbms_stats.gather_table_stats(user,'TAB3');

explain plan for
select /*+ LEADING(t2 t3 t1) */   count(*) 
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;

explain plan for
select /*+ LEADING(t2 t3 t1) */   count(*) 
from tab1 t1 ,tab2 t2, tab3 t3
where t1.col1 = t2.col1
and t1.col1 = t3.col1
;


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

→ t2,t3の結合はMERGE JOIN CARTESIANとなり、結合条件の推移律はない★

 

(14)

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
  );
drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 bigint
   );
   
drop table tab3;
create table tab3(
    col1 bigint 
   ,col2 bigint
   );

start transaction;
insert into tab1 select
   g
  ,floor(random() * 100000)+1
from generate_series(1,1000000) g;

commit;

start transaction;
insert into tab2 select
   g
  ,floor(random() * 100000)+1
from generate_series(1,1000000) g;

commit;

start transaction;
insert into tab3 select
   g
  ,floor(random() * 100000)+1
from generate_series(1,1000000) g;

commit;

\timing 1
\pset pager 0
set pg_hint_plan.debug_print=1;
show pg_hint_plan.debug_print;


analyze tab1;
analyze tab2;
analyze tab3;

/*+ Leading(( (t2 t3) t1)) */ 
explain 
select   count(*) 
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;

→ Hash Cond: (t2.col1 = t3.col1)となり、結合条件の推移律はある

(2019)

drop table tab1;
create table tab1(
    col1 bigint not null
   ,col2 bigint
   );

drop table tab2;
create table tab2(
    col1 bigint not null
   ,col2 bigint
   );


drop table tab3;
create table tab3(
    col1 bigint not null
   ,col2 bigint
   );

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


update statistics tab1;
update statistics tab2;
update statistics tab3;

set showplan_all on
go
select   count(*) 
from tab2 t2, tab3 t3, tab1 t1
where t1.col1 = t2.col1
and t1.col1 = t3.col1
OPTION (FORCE ORDER)
;
go
set showplan_all off
go

→ HASH:([t2].[col1])=([t3].[col1]) となり結合条件の推移律はある