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.htm 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

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