Re: Materialized Views

From: Jared Still <>
Date: Tue, 23 Dec 2008 11:33:19 -0800
Message-ID: <>

On Tue, Dec 23, 2008 at 9:57 AM, Robertson Lee - lerobe <> 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:


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
tablespace mv_data
with rowid

create index "MLOG$_SIGNOFF_IDX1"
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 database, and adding the index is still necessary.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Received on Tue Dec 23 2008 - 13:33:19 CST

Original text of this message