RE: Materialized Views

From: Robertson Lee - lerobe <Lee.Robertson_at_acxiom.com>
Date: Wed, 24 Dec 2008 08:50:14 -0000
Message-ID: <F1A191B056E5E04EA2134D842F9396F815216C25@sunmsx01.Corp.Acxiom.net>

Many thanks and a very Merry Xmas and a Happy, healthy 2009 to everyone on here  

Cheers  

Lee

---

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: 23 December 2008 19:33
To: Robertson Lee - lerobe
Cc: oracle-l
Subject: Re: Materialized Views

On Tue, Dec 23, 2008 at 9:57 AM, Robertson Lee - lerobe <Lee.Robertson_at_acxiom.com> wrote:

        Oracle 10g R2          

        Hi,          

        Anyone point me in the direction of some good reading material with respect to the above please. Looks like we may be attempting to use these for some complex aggregtations in some databases. Any gotchas/restrictions etc. would be much appreciated.          

You may want to start here:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/basicmv.h tm
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/advmv.htm

Gotchas:

There's probably more than I know of, I only use MV's at a basic level.

Here's one:

Assuming the following:

• fast refresh
• MV logs - as required by fast refresh
• there is already at least one MV built using the MV log
• the new MV is on a different server or in a different refresh group

Refreshes of existing MV's against the existing MV log must be stopped, failure to do so may result in this error:

12034, 0000, "materialized view log on \"%s\".\"%s\" younger than last refresh"

// *Cause:  The materialized view log was younger than the last refresh.
// *Action: A complete refresh is required before the next fast refresh.
//

This occurs when MV B is being built against MV log A, and during that build time, existing MV A refreshes, causing rows to be deleted from the MV log, before the build of B is complete.

Hope that makes some sort of sense.

Here's one more:

When creating MV logs, always add an index on SNAPTIME$$ column.

Here's an example:
( old syntax here, but it's an old script, and it still works)

create snapshot log
on "SIGNOFF"
tablespace mv_data
with rowid
/

create index "MLOG$_SIGNOFF_IDX1"
on "MLOG$_SIGNOFF" (SNAPTIME$$)
tablespace mv_data
/

There's an ML note about this if you care to look for it, but the purpose of this index
is to greatly reduce the IO incurred by DELETEs on the MV log tables.

I just built new MVs in an 11.1.0.7 database, and adding the index is still necessary.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

---

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

---

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 24 2008 - 02:50:14 CST