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-----
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 20, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Weekly aggregates
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).
