Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> REPOST: partition views

REPOST: partition views

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Mon, 16 Jul 2001 12:16:48 -0400
Message-ID: <ep46lt00v09qpaneceb0q5497ghlf5ifpl@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)
Received on Mon Jul 16 2001 - 11:16:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US