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