プルーニング条件の推移律

内部結合した別テーブルに指定された条件を推移的に使用して
インデックスアクセス時に
パーティションプルーニングできるか確認

→ いずれのDBも有効

(8.0.29)


drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
   ,col3 bigint
)
 partition by hash ( col1 )
 ( partition p0
 , partition p1
 );

-- alter table tab1 add constraint tab1pk primary key(col1,col2);


drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 bigint
   ,col3 bigint
)
 partition by hash ( col1 )
 ( partition p0
 , partition p1
 );
 
-- alter table tab2 add constraint tab2pk primary key(col1,col2);

drop table tab3;
create table tab3(
    col1 bigint 
   ,col2 bigint
   ,col3 bigint
)
 partition by hash ( col1 )
 ( partition p0
 , partition p1
 );
 
-- alter table tab3 add constraint tab3pk primary key(col1,col2);


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(
      floor(rand() * 10)+1
    , i
    , floor(rand() * 1000000)+1
    );
    insert into tab2 values(
      floor(rand() * 10)+1
    , i
    , floor(rand() * 1000000)+1
    );
    insert into tab3 values(
      floor(rand() * 10)+1
    , i
    , floor(rand() * 1000000)+1
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);

create index ind13 on tab1(col3);
create index ind23 on tab2(col3);
create index ind33 on tab3(col3);

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

explain analyze
select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t1.col1 = 10
;

→ (actual time=0.017..256.186 rows=499816 loops=1)
 パーティションプルーニング有効、Table scan + Inner hash join

explain analyze
select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t3.col3 = 188245
;

→ (actual time=0.022..0.046 rows=2 loops=1)
 パーティションプルーニング無効、Index lookup + Inner hash join


explain analyze
select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t1.col1 = 10
and t3.col3 = 188245
;

→ (actual time=0.031..0.039 rows=1 loops=1)
 rowsの件数が1件に減っており、パーティションプルーニング有効 、Table scan + Inner hash join


select count(*) from tab3 where col3 = 188245;
select count(*) from tab3 partition(p0) where col3 = 188245;
select count(*) from tab3 partition(p1) where col3 = 188245;

結果:
t1に指定したプルーニング条件は結合条件による推移律によりt3でも有効

 

 

(19c)

drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 int
   ,col3 int
  )
partition by hash (col1)
( partition p0
, partition p1
);
alter table tab1 add constraint tab1pk primary key(col1,col2);


drop table tab2 purge;
create table tab2(
    col1 int 
   ,col2 int
   ,col3 int
  )
partition by hash (col1)
( partition p0
, partition p1
);
alter table tab2 add constraint tab2pk primary key(col1,col2);

drop table tab3 purge;
create table tab3(
    col1 int 
   ,col2 int
   ,col3 int
  )
partition by hash (col1)
( partition p0
, partition p1
);
alter table tab3 add constraint tab3pk primary key(col1,col2);

 


declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
     floor(dbms_random.value(1, 11) )
   , i
   , floor(dbms_random.value(1, 1000001) )
   );
  insert into tab2 values(
     floor(dbms_random.value(1, 11) )
   , i
   , floor(dbms_random.value(1, 1000001) )
   );
  insert into tab3 values(
     floor(dbms_random.value(1, 11) )
   , i
   , floor(dbms_random.value(1, 1000001) )
   );
end loop;
end;
/

commit;

create index ind13 on tab1(col3) local;
create index ind23 on tab2(col3) local;
create index ind33 on tab3(col3) local;

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 t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t1.col1 = 2
;
select * from table(dbms_xplan.display(format=>'ALL') );


 パーティションプルーニング有効、TABLE ACCESS FULL + HASH JOIN

explain plan for
select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t3.col3 = 376565
;
select * from table(dbms_xplan.display(format=>'ALL') );


 パーティションプルーニング無効、INDEX RANGE SCAN + NESTED LOOPS

explain plan for
select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t1.col1 = 2
and t3.col3 = 376565
;
select * from table(dbms_xplan.display(format=>'ALL') );


 パーティションプルーニング有効、INDEX RANGE SCAN + NESTED LOOPS


結果:
t1に指定したプルーニング条件は結合条件による推移律によりt3でも有効

 

(14)

drop table tab1;
create table tab1(
    col1 bigint
   ,col2 bigint
   ,col3 bigint
  )
partition by hash(col1);
alter table tab1 add constraint tab1pk primary key(col1,col2);

create table tab1p0 partition of tab1 for values with (modulus 2,remainder 0);
create table tab1p1 partition of tab1 for values with (modulus 2,remainder 1);


drop table tab2;
create table tab2(
    col1 bigint
   ,col2 bigint
   ,col3 bigint
   )
partition by hash(col1);
alter table tab2 add constraint tab2pk primary key(col1,col2);

create table tab2p0 partition of tab2 for values with (modulus 2,remainder 0);
create table tab2p1 partition of tab2 for values with (modulus 2,remainder 1);

drop table tab3;
create table tab3(
    col1 bigint
   ,col2 bigint
   ,col3 bigint
   )
partition by hash(col1);
alter table tab3 add constraint tab3pk primary key(col1,col2);

create table tab3p0 partition of tab3 for values with (modulus 2,remainder 0);
create table tab3p1 partition of tab3 for values with (modulus 2,remainder 1);

 

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

commit;

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

commit;

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

commit;


create index ind13 on tab1(col3);
create index ind23 on tab2(col3);
create index ind33 on tab3(col3);


\timing 1
\pset pager 0


analyze tab1;
analyze tab2;
analyze tab3;

explain analyze
select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t1.col1 = 10
;


 Planning Time: 0.436 ms
 Execution Time: 208.108 ms
 パーティションプルーニング有効、Bitmap Index Scan + Hash Join

explain analyze
select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t3.col3 = 565800
;


 Planning Time: 0.454 ms
 Execution Time: 0.115 ms
 パーティションプルーニング無効、Index Scan + Nested Loop
 
explain analyze
select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t1.col1 = 10
and t3.col3 = 565800
;


 Planning Time: 0.361 ms
 Execution Time: 0.110 ms
 パーティションプルーニング有効、Index Scan + Nested Loop


結果:
t1に指定したプルーニング条件は結合条件による推移律によりt3でも有効

 

(2019)


use test
go

drop partition scheme ps1;
drop partition function pf1;

create partition function pf1(bigint) as range right for values (50);
create partition scheme ps1 as partition pf1 all to ([primary]);

 

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

-- alter table tab1 add constraint tab1pk primary key(col1,col2);


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

-- alter table tab2 add constraint tab2pk primary key(col1,col2);


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

-- alter table tab3 add constraint tab3pk primary key(col1,col2);

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

create index ind13 on tab1(col3) on ps1(col1);
create index ind23 on tab2(col3) on ps1(col1);
create index ind33 on tab3(col3) on ps1(col1);

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

 

select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t1.col1 = 5
;
→ 読み取った行数 = 490140 、Table Scan、パーティションプルーニング有効

select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t3.col3 = 72180
;
→ 読み取った行数 = 4 、Index Seek、パーティションプルーニング無効

select t1.col1,t1.col2,t1.col3,t2.col3,t3.col3
from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
inner join tab3 t3
on t1.col1 = t3.col1 and t1.col2 = t3.col2
where t1.col1 = 5
and t3.col3 = 72180
;

→ 読み取った行数 = 3 、Index Seek、パーティションプルーニング有効

 

結果:
t1に指定したプルーニング条件は結合条件による推移律によりt3でも有効