Re: REPOST: partition views

On Mon, 16 Jul 2001 16:27:18 -0700, "Mark B. Townsend" <Mark.Townsend_at_oracle.com> wrote:

>What partition view would you expect the query 'select count(*) from v1'
>to eliminate ?

It was a type-o. The query would be something like

select count(*) from v1 where start_time = <some date value>;

What I get is no partition elimination, and a full scan on all tables in the view.

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

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