Re: REPOST: partition views

"Chuck Hamilton" <chuck_hamilton_at_yahoo.com> wrote in message news:ep46lt00v09qpaneceb0q5497ghlf5ifpl_at_4ax.com...
> 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)

According to the book of Jonathan Lewis, partition views are on the way out.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Mon Jul 16 2001 - 11:52:57 CDT