Re: REPOST: partition views

In article <ep46lt00v09qpaneceb0q5497ghlf5ifpl_at_4ax.com>, Chuck says...
>
>I'm working on a project that requires partitioning but must run on an
>8i standard edition database (i.e. no partition option). I want to use
>the old 7.3 style partition view approach but the optimizer is not
>eliminating partitions (tables) from the view. I've set
>partition_view_enabled to true, created the tables and view, and put
>constraints on what would be the paritition key in each table to limit
>what values can exist in that table.
>
>The ddl is something like this...
>

can you post the exact DDL (shortened version, just enough to reproduce). lets see date formats et. al.

>create table t1 (start_time date, ....
> constraint parkey1
> check (start_time between <date1 00:00:00> and <date1 23:59:59>));
>
>create table t2 (start_time date, ....
> constraint parkey2
> check (start_time between <date2 00:00:00> and <date2 23:59:59>));
>
>create view v1 as select * from t1 union all select * from t2;
>
>When I run a query like - select count(*) from v1 - the autotrace
>stats show that I'm scanning both tables. The explain plan show's
>nothing about partition elimination either. Refresh my memory please.
>What am I missing to cause partition elimination?
>--
>Chuck Hamilton
>chuck_hamilton_at_yahoo.com
>
>"Do not be deceived, God is not mocked;
>for whatever a man sows, this he will also
>reap." (Gal 6:7 NASB)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp