Tuning - using more than one partition

From: Volker Hauswurz <volker.hauswurz_at_materna.de>
Date: Wed, 1 Mar 2006 15:01:12 +0100
Message-ID: <du49f8$joi$1@pentheus.materna.de>

Hello,

we are using

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning and Data Mining options

on Solaris 10. (The problem described seems to be the same on 9.2.0.5.)

We have to retrieve data in one or more than one partitions - it depends.

At runtime we insert the information about the partitions which have to be used by temp table as you can see:

drop table test
/

drop table tmp
/

create table test(p number, n1 number, n2 number, n3 char(2000)) partition by range (p)
(partition p1 values less than(2),
partition p2 values less than(3),
partition p3 values less than(4)
)
/

begin
for i in 1..1000 loop

insert into test values(1,i, i, to_char(i));
insert into test values(2,i, i, to_char(i));
insert into test values(3,i, i, to_char(i));

end loop;
commit;
end;
/

create index i on test(n1,p) local
/

create index i3 on test(n2,p) local
/

/*- the temp table----*/

create global temporary table tmp (p number) on commit preserve rows
/

create unique index i_tmp on tmp(p)
/

begin
dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'TEST', cascade=>true);
end;
/

begin
dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'TMP', cascade=>true);
end;
/

create or replace view vap as select test.* from tmp, test where test.p =tmp.p
/

delete from plan_table
/

explain plan for select * from vap v1, vap v2 where v1.n2=:b1
and v1.n1=v2.n1
/

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'serial'))
/

• Here we see PARTITION RANGE (ITERATOR) 2 times which is what we expected
• so at runtime just the important partitions get to be visited

delete from plan_table
/

explain plan for select * from vap v1, vap v2, vap v3 where v1.n2=:b1
and v1.n1=v2.n1
and v1.n1=v3.n1
/

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'serial'))
/

Here we get

....

| 6 | PARTITION RANGE ALL | | | | | 1 | 3 | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 3 | 6033 | 2 (50)| 1 | 3 |* 8 | INDEX RANGE SCAN | I3 | 3 | | 4 (0)| 1 | 3 | | 9 | PARTITION RANGE ALL | | | | | 1 | 3 | | 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 3 | 6033 | 2 (50)| 1 | 3 |* 11 | INDEX RANGE SCAN | I | 3 | | 3 (0)| 1 | 3 | | 12 | PARTITION RANGE ALL | | | | | 1 | 3 | | 13 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST | 3 | 6033 | 2 (50)| 1 | 3 |* 14 | INDEX RANGE SCAN | I | 3 | | 3 (0)| 1 | 3 | ...

• All three partitions will be visited which we did not expect

Why does this happen? Can we do something about it?

Thanks in advance

Volker Received on Wed Mar 01 2006 - 08:01:12 CST