I think I need to read more on the MVs. Right now I'm
very unclear how can they add something to a prebuild
table. I thought they could replace an aggregate.
Thanks for the advice, everyone (Paula, Beth, Dennis,
Darrel, hope I didn't miss anyone). I might revive
this thread when I get some more
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: Multiple recipients of list ORACLE-L
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
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
Title: RE: Weekly aggregates
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
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
One of the options for materialized views
is to 'create MV on prebuilt table'
If you want to use the table for direct queries,
then you can - if you enable query rewrite then
suitable queries against the base tables can
be rewritten to take advantage of the MV.
It's a technique I've advised
I second this.
Last year a developer put together a scenario in one of our data warehouses involving
a big base table, hierarchies, and 8 MVs as summaries of that data, with query
rewrite, etc. Supporting it has taken some getting used to, but now I like it and am
on the lookout to take
