Re: Oracle 8i - Materialized Views

Nice to be proved right once in a while

--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)

                        <KevinALewis_at_HotMail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:926702091.25063.1.nnrp-12.9e984b29_at_news.demon.co.uk...
> Correct - materialized views are actually snapshots.
> In fact, v7 snapshots have been subsumed into the
> materialized view area.
>
> Materialized views can be fast or full refresh, just
> as snapshots were, or they can actually be defined
> as "end user's problem" i.e. they will be there if
> Oracle wants to use them, but it doesn't try to
> maintain them in any way.
>
> There primary benefit is in the query-rewrite code.
> if you define 'dimensions' and give information to
> oracle about the association between materialized
> views and large fact tables, then oracle can
> transparently rewrite the query against the smaller
> materialized view.
>
> I haven't stress-tested the technology yet, but
> I have a strong bias against automated refresh
> of such tables, as I have often found that a
> cunning bit of SQL to pack the data in a suitable
> order does wonders for the end-user performance.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Kevin A Lewis wrote in message
> >Not that I have looked into it but my suspicion on b) is that to get this
> >type of performance increase the 'Materialize' must mean 'Written
> >Somewhere'. If this is the case the big downside would be on DML (or
> update)
> >activity on the base tables leading to update activity somewhere else.
>
>
> ><dbhals_at_my-dejanews.com> wrote in message
> >news:7hhag1$5qa$1_at_nnrp1.deja.com...
> >> I'm currently working on a DW project using
> >> Oracle 8i. One aspect which I'm looking at is
> >> Materialized views.
> >>
> >> My questions are:
> >> a) has anyone used MV in a DW or any other env.
> >> b) What are the pit falls (if any)
>
>
>
Received on Mon May 17 1999 - 03:16:46 CDT