RE: Materialized Views
Date: Wed, 24 Dec 2008 08:50:14 -0000
Many thanks and a very Merry Xmas and a Happy, healthy 2009 to everyone on here
From: Jared Still [mailto:jkstill_at_gmail.com]
Sent: 23 December 2008 19:33
To: Robertson Lee - lerobe
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
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:
There's probably more than I know of, I only use MV's at a basic level.
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
create index "MLOG$_SIGNOFF_IDX1"
on "MLOG$_SIGNOFF" (SNAPTIME$$)
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 220.127.116.11 database, and adding the index is still necessary.
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.
Received on Wed Dec 24 2008 - 02:50:14 CST