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 ----- From: "Gurelei" <gurelei_at_yahoo.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 20 March 2003 19:18 Subject: Weekly aggregates

> 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
Received on Thu Mar 20 2003 - 14:26:49 CST