RE: Materialized Views
- From: "Robertson Lee - lerobe" <Lee.Robertson@xxxxxxxxxx>
- To: "Jared Still" <jkstill@xxxxxxxxx>
- 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
Cheers
Lee
________________________________
From: Jared Still [mailto:jkstill@xxxxxxxxx]
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@xxxxxxxxxx> 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.
****************************************************************************
Other related posts:
