Re: REPOST: partition views

Chuck Hamilton wrote:
>
> 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...
>
> 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)

I don't want to appear insulting - but you've analyzed all the appropriate bits...?

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."