(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(t1 ,t2 ,t3) */ count(*)
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t2.col1 = t3.col1
where t3.col1 = 123
;
show warnings;
→ Filter: (t1.col1 = 123)となっており、where条件の推移律はある
(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(t1 t2 t3) */ count(*)
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t2.col1 = t3.col1
where t3.col1 = 123
;
explain plan for
select /*+ LEADING(t1 t2 t3) */ count(*)
from tab1 t1 ,tab2 t2, tab3 t3
where t1.col1 = t2.col1
and t2.col1 = t3.col1
and t3.col1 = 123
;
select * from table(dbms_xplan.display(format=>'ALL') );
→ filter("T1"."COL1"=123)となっており、where条件の推移律はある
(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( t1 t2 t3 ) */
explain analyze
select count(*)
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t2.col1 = t3.col1
where t3.col1 = 123
;
→ Filter: (col1 = 123)となっており、where条件の推移律はある
(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 tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t2.col1 = t3.col1
where t3.col1 = 123
OPTION (FORCE ORDER ,MAXDOP 1)
;
go
set showplan_all off
go
→ WHERE:([test].[dbo].[tab1].[col1] as [t1].[col1]=(123))となっており、where条件の推移律はある
