Oracle – What is the Update Trigger Property in Materialized View?

materialized-vieworacleoracle-18ctrigger

I've created a materialized view in an Oracle 18c database.

When I look at the Info tab of the materialized view in Toad, I see that there is a property called Update Trigger.

enter image description here

What does the Update Trigger property represent?

Best Answer

Those are the remains of an ancient feature called updatable snaphots that later became updatable materialized views.

Originally materialized views were called snapshots. The values for Update Trigger and Update Log come from the UPDATE_TRIG and UPDATE_LOG columns of ALL|DBA|USER_SNAPSHOTS, a view that was last documented properly in Oracle 8i: ALL_SNAPSHOTS. The 9.0 documentation of that view is actually broken.

Oracle 7: Snapshot Site Replication

Updatable Snapshot Architecture

In addition to the objects created for read-only snapshots that are described [*], when you create an updatable snapshot, two additional objects are created at the snapshot site:

  • Oracle creates a table, named USLOG$_snapshot_name, to store the ROWID and timestamp of rows updated in the snapshot. The timestamp
    column is not updated until the log is first used by a snapshot
    refresh.

  • Oracle creates an AFTER ROW trigger on the snapshot base table to insert the ROWIDs and timestamps of updated and deleted rows into the updatable snapshot log. The trigger is named USTRG$_snapshot_name.

In Oracle 8i, snapshots were renamed to materialized views, and updatable snapshots became updatable materialized views. The above allowed users to perform DML operations on snapshots/materialized views, and those changes could be replicated back to the master tables.

Updatable materialized views were part of a feature called Advanced Replication. That is a feature that I have yet to see in a real database. About 6 years ago I found a 10.2 customer database in which someone tried to set up this feature in 2008 but failed to do it. I have never ever seen this anywhere else.

Advanced Replication became deprecated in 12.1, and desupported in 12.2.

Related Question