Re: Partition view not working - help!

From: Ian Cary <cary_at_gatwick.geco-prakla.slb.com>
Date: 1997/11/07
Message-ID: <34630A82.EFE6FBCD@gatwick.geco-prakla.slb.com>#1/1

Bruce,

I've seen a few postings about partitioned views in the newsgroups and your problem interested me so I thought I'd test it to see what I got. Here is what I did (apologies if I misstype anything) to try and simulate your situation;

create table tab1(fld1 not null,fld2 not null,fld3 varchar2(10));
create table tab2(fld1 not null,fld2 not null,fld3 varchar2(10));
create table tab3(fld1 not null,fld2 not null,fld3 varchar2(10));

alter table tab1 add constraint t1_pk primary key(fld1,fld2);

alter table tab2 add constraint t2_pk primary key(fld1,fld2); alter table tab3 add constraint t3_pk primary key(fld1,fld2);

alter table tab1 add constraint t1_chk
check (fld2 between 1 and 3);
alter table tab2 add constraint t1_chk
check (fld2 between 4 and 6);
alter table tab3 add constraint t1_chk
check (fld2 between 7 and 9);

create view tabs as
select * from tab1
union all
select * from tab2
union all
select * from tab3;

analyze table tab1 compute statistics;
analyze table tab2 compute statistics;
analyze table tab3 compute statistics;

I then issued the following

explain plan for
select * from tabs
where fld2 = 1
and fld1 =1;

which returned:-

SELECT STATEMENT Cost = 1
  2.1 VIEW TABS
    3.1 UNION-ALL PARTITION

      4.1 INDEX UNIQUE SCAN T1PK UNIQUE
      4.2 FILTER
        5.1 INDEX UNIQUE SCAN T2PK UNIQUE
      4.3 FILTER
        5.1 INDEX UNIQUE SCAN T3PK UNIQUE

and also;

explain plan for
select * from tabs
where fld2 = 4
and fld1 =1;

which returned:-

SELECT STATEMENT Cost = 1
  2.1 VIEW TABS
    3.1 UNION-ALL PARTITION

      4.1 FILTER
        5.1 INDEX UNIQUE SCAN T1PK UNIQUE
      4.2 INDEX UNIQUE SCAN T2PK UNIQUE
      4.3 FILTER
        5.1 INDEX UNIQUE SCAN T3PK UNIQUE

The plans obviously indicate that a partitioned view is being used, but the filter statements also indicate which partitions are going to skipped. As can be seen from these two examples the execution plan would appear to be accessing only the correct partition in each case. This would seem to indicate that your Oracle representative was incorrect when they said that the where clause could only contain the column on which the view was based (unless they were referring to the view creation step itself)

I hope you and anyone else reading this posting finds the information useful.

Regards,

Ian Received on Fri Nov 07 1997 - 00:00:00 CST