Paula, I'm not sure what would an MV on top of an aggregate give me. I mean, I have a base table and I need to aggregate. I can see doing it via an aggregate table or (as someone suggested) via an MV. But you are suggesting both. What benefit do you see from that? What am I missing?
thanks --- [EMAIL PROTECTED] wrote: > What about materialized view on top of the > partitioned table aggregated by > week? I don't remember is there a restriction that > the material. view must > be partitioned same as table? If not it might be a > bit of a perf. hit but > only once. Also, can determine how to populate the > mat. view and if you > just get a week at a time can set it up so that just > those changes are > pushed out to the mater. view. - Just a thought. > > Oracle OCP DBA > > > -----Original Message----- > Sent: Thursday, March 20, 2003 3:24 PM > To: Multiple recipients of list ORACLE-L > > > > I've used both methods at different sites for > different reasons. > > If you need the performance of the > partition-wise join, then you keep > the weekly aggregates in monthly > partitions, and work around the > problems of not being able to do > a single week > create as select / exchange partition > > If the performance is adequate without > partitionwise joins, but you need to > be able to build the aggregates as > rapidly as possible, then use weekly > partitions. 3 years at weekly partitions > is only 150 - 160 partitions - even with > a handful of indexes, that shouldn't be > a problem at parse time. > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Now available One-day tutorials: > Cost Based Optimisation > Trouble-shooting and Tuning > Indexing Strategies > > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > ____UK_______April 8th > ____UK_______April 22nd > > ____Denmark May 21-23rd > > ____USA_(FL)_May 2nd > > > Next dates for the 3-day seminar: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > ____UK_(Manchester)_May > ____USA_(CA, TX)_August > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: 20 March 2003 19:18 > > > > Hi all: > > > > An application I'm supporting needs weekly > aggregates. > > Nothing wrong with that except I'm thinking of how > to > > partition that aggregate table. The requirement is > to > > keep 3 year history of data. I have been > partitioning > > other aggregate tables (monthly etc) by month. > This > > makes it easy to drop old partitions AND Oracle > can > > use the partitions to reduce the size of the data > for > > some queries. I'd like to keep the montly > partitioning > > in for the uniformity reasons, but weeks do not > lay > > over months, a week can span two months and > therefore > > the usefulness of partitions for some of the > reports > > will be reduced. I'm wondering how do others > approach > > this. Do people partition weekly aggregates by > week > > instead of months? ANy other thoughts? > > > > thanks > > > > Gene > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > __________________________________________________ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
