Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Weekly aggregates

Re: Weekly aggregates

From: Darrell Landrum <>
Date: Thu, 20 Mar 2003 20:08:09 -0600
Message-Id: <>

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 advantage of this technology in other areas.

>>> 03/20/03 05:54PM >>>

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 clients to use when materialized views first appeared and they already had a big investment in code to create aggregate tables. Prebuilt tables are also my favourite option for MVs - you don't get the awful unpredictable that can appear with dynamic refreshes.


Jonathan Lewis

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ

> 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?

Please see the official ORACLE-L FAQ: 
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051 
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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
Received on Thu Mar 20 2003 - 20:08:09 CST

Original text of this message