Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: Weekly aggregates

From: Darrell Landrum <dlandrum_at_zalecorp.com>
Date: Thu, 20 Mar 2003 20:08:09 -0600
Message-Id: <24726.322606@fatcity.com>


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

> 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
Received on Thu Mar 20 2003 - 20:08:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US