Re: Materialized Views
Date: Tue, 23 Dec 2008 11:33:19 -0800
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
is to greatly reduce the IO incurred by DELETEs on the MV log tables.
I just built new MVs in an 18.104.22.168 database, and adding the index is still necessary.
Certifiable Oracle DBA and Part Time Perl Evangelist