Learn from the mistakes of others – you can never live long enough to make them all yourself…

Tag Archives: materialized views

As I’m still trying to get my feet under the door at my new gig, here’s another nugget in place of a real blog entry. While troubleshooting for a client, I noted that there were three types of materialized view. I knew this before, sort of, but this troubleshooting exercise was a great aide memoire.

The three types of materialized views:

Read-only

Updateable

Writeable

The read-only MV omits the FOR UPDATE clause in its DDL during creation and does not permit DML.

The updateable MV includes the FOR UPDATE clause in its DDL and is included in a materialized view group. This allows changes made to the MV to be pushed back to the ‘master’ during a refresh.

The writeable MV includes the FOR UPDATE clause in its DDL, but does not belong to a materialized view group. All changes made to the MV are lost during a refresh.