Re: Weekly aggregates

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.

>>> jonathan_at_jlcomp.demon.co.uk 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.

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 22:09

> 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: 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
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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