Re: REPOST: partition views

On Mon, 16 Jul 2001 18:52:57 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>
>"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.
>

That's cool, but as of 8.1.7 to the best of my knowledge they haven't been removed. I'd like to implement them for now. What am I doing wrong?

--
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)