Re: Materialized Views
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: Lee.Robertson@xxxxxxxxxx
- Date: Tue, 23 Dec 2008 11:33:19 -0800
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.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
Other related posts:
