| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Weekly aggregates
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
--- Paula_Stankus_at_doh.state.fl.us 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"
> <ORACLE-L_at_fatcity.com>
> 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: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
 |
 |