内部結合した別テーブルに指定された条件を推移的に使用して
インデックスアクセス時に
パーティションプルーニングできるか確認
→ いずれの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でも有効