30.1.07

Unbreakable Materialized Views

Oracle8i: Invalidating a Materialized View

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DDL operation, such as a DROP or ALTER, on any dependency in the materialized view will cause it to become invalid.

Oracle9i: Invalidating Materialized Views

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DML operation, such as a INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

Oracle10g: Invalidating Materialized Views

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

RTFM

In 9i and 10g, a materialized view becomes automatically invalid when performing DML or DDL. Don't worry:

A materialized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transparently revalidated.

Suppose you have a materialized view on EMP and your application aborts immediate after logon when invalid objects are found. The "early bird" user modifies the EMP table and all others users cannot start the application anymore. I hear the sound of a ringing phone...

2 comments:

Theo said...

I have been thinking about this "changed feature" for materialized views, but I just cannot think of any reason why a materialized view should become an invalid object when data has changed in a dependent table. I like the Oracle 8i implementation better than the 9i/10g implementation.

Anonymous said...

Oddly enough I'm getting the 9+ dependency-DML => mview invalidation behaviour on an 8.1.7.4

The docs you reference for 8i actually belong to 8.1.6. The warehouse manual was NOT updated for 8.1.7, and the addendums and release notes don't say anything about it... but still it's what I'm seeing: as soon as someone DMLs on a dependency, the materialized view goes invalid.

Metalink also didn't help... does anybody else see this on 8.1.7? Any docs that confirm that 8.1.7 behaves like 9+ on DML-invalidation?