Re: ?partition views?

For a partition view to work, you have to specify 'select *' for every table in the create view statement (or select col1,col2,... thereby referencing every column).

This is not explicitly stated in the tuning manual, but it is described in some Notes on the Oracle Metalink.

Jaap.

Sandimirov P.Andrew wrote in message <861b70$7l1$1_at_ss20.vaz.ru>...
>Hi.
>Oracle 7.3.2
>i testing partition view how in example "Oracle7 Server Tuning".
>but in my query plan i can not look "FILTER":
>
>QUERY_PLAN
>-----------------------------
> VIEW TX 1
> UNION-ALL PARTITION 1
> TABLE ACCESS FULL T1
> TABLE ACCESS FULL T2
> TABLE ACCESS FULL T3
> TABLE ACCESS FULL T4
> TABLE ACCESS FULL T5
> TABLE ACCESS FULL T6
>
>What wrong??!!
>Help please.
>--------------------------------------
>create table t1(idtmc number(5),mm date,kolvo number);
>create table t2(idtmc number(5),mm date,kolvo number);
>create table t3(idtmc number(5),mm date,kolvo number);
>create table t4(idtmc number(5),mm date,kolvo number);
>create table t5(idtmc number(5),mm date,kolvo number);
>create table t6(idtmc number(5),mm date,kolvo number);
>rem load 100 records into each tables
>rem @load
>alter table t1 add constraint t1_chk check (mm between
>to_date('01/01/2000','dd/mm/yyyy') and to_date('31/01/2000','dd/mm/yyyy'));
>alter table t2 add constraint t2_chk check (mm between
>to_date('01/02/2000','dd/mm/yyyy') and to_date('29/02/2000','dd/mm/yyyy'));
>alter table t3 add constraint t3_chk check (mm between
>to_date('01/03/2000','dd/mm/yyyy') and to_date('31/03/2000','dd/mm/yyyy'));
>alter table t4 add constraint t4_chk check (mm between
>to_date('01/04/2000','dd/mm/yyyy') and to_date('30/04/2000','dd/mm/yyyy'));
>alter table t5 add constraint t5_chk check (mm between
>to_date('01/05/2000','dd/mm/yyyy') and to_date('31/05/2000','dd/mm/yyyy'));
>alter table t6 add constraint t6_chk check (mm between
>to_date('01/06/2000','dd/mm/yyyy') and to_date('30/06/2000','dd/mm/yyyy'));
>
>CREATE INDEX I_T1$idtmc ON T1(idtmc) TABLESPACE FOR_INDEX;
>CREATE INDEX I_T2$idtmc ON T2(idtmc) TABLESPACE FOR_INDEX;
>CREATE INDEX I_T3$idtmc ON T3(idtmc) TABLESPACE FOR_INDEX;
>CREATE INDEX I_T4$idtmc ON T4(idtmc) TABLESPACE FOR_INDEX;
>CREATE INDEX I_T5$idtmc ON T5(idtmc) TABLESPACE FOR_INDEX;
>CREATE INDEX I_T6$idtmc ON T6(idtmc) TABLESPACE FOR_INDEX;
>
>ANALYZE TABLE T1 COMPUTE STATISTICS;
>ANALYZE TABLE T2 COMPUTE STATISTICS;
>ANALYZE TABLE T3 COMPUTE STATISTICS;
>ANALYZE TABLE T4 COMPUTE STATISTICS;
>ANALYZE TABLE T5 COMPUTE STATISTICS;
>ANALYZE TABLE T6 COMPUTE STATISTICS;
>
>create or replace view tx as
>select idtmc,mm,kolvo from t1
>union all
>select idtmc,mm,kolvo from t2
>union all
>select idtmc,mm,kolvo from t3
>union all
>select idtmc,mm,kolvo from t4
>union all
>select idtmc,mm,kolvo from t5
>union all
>select idtmc,mm,kolvo from t6;
>
>explain plan for select * from tx where
>mm=to_date('04/06/2000','dd/mm/yyyy');
>@makeplan
>
>
Received on Tue Jan 18 2000 - 06:22:29 CST