Re: Oracle 8i - Materialized Views

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 Fri May 14 1999 - 12:11:53 CDT