RE: Materialized Views
- From: "Michael Fontana" <mfontana@xxxxxxxxxxx>
- To: "'Niall Litchfield'" <niall.litchfield@xxxxxxxxx>
- Date: Wed, 24 Dec 2008 09:47:48 -0600 (CST)
Point well taken, Niall. I had forgotten that we had used this
aggregation method in a DW setting, and it is certainly apt in such a
situation.
Of course, I still recall a colleague scorning such usage. One problem
you can have if they are not well-documented is that they can cause
internal database performance issues if not monitored and tuned, and
constantly verified as pertinent. What I'm getting at is that as business
requirements change, or data shapes change, such reporting type MVs can
become obsolete.
I'd list this as another major "gotcha" in their use.
_____
From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Sent: Wednesday, December 24, 2008 4:46 AM
To: mfontana@xxxxxxxxxxx
Cc: Lee.Robertson@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Materialized Views
On Tue, Dec 23, 2008 at 8:18 PM, Michael Fontana <mfontana@xxxxxxxxxxx>
wrote:
You need to know more about a feature before you can truly decide it's
required.
While it's allowed and there may be some valid reasons for it, often
there are not. MVs are most often used to create distributed versions of
data. T
Hi Michael
Complex aggregations, or rather complex aggregations that do not perform
sufficiently raidly but which can be pre-calculated, are the other major
reason for this feature in a reporting/dss type environment. To quote from
the DataWarehousing guide
One technique employed in data warehouses to improve performance is the
creation of summaries. Summaries are special types of aggregate views that
improve query execution times by precalculating expensive joins and
aggregation operations prior to execution and storing the results in a
table in the database. For example, you can create a summary table to
contain the sums of sales by region and by product.
So it rather sounded to me that Lee's developers reasons for using MVs
looked to be not unreasonable. Having said all that, I;ve only ever really
dealt with the remote view use for them, and you are absolutely right
about the detection of out of date snapshots in that context.
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
