Re: ?partition views?

The create view statement is adequate -

    it selects all the columns in the declared order. You will also note that Oracle recognises in the plan that the view is a partition view.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jaap W. van Dijk wrote in message <861r3e$coe$1_at_hdxl22.telecom.ptt.nl>...
>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 - 13:19:30 CST