How Oracle uses triggers to customize materialized view refreshes

The refresh of materialized views is actually not fundamentally different from normal SQL execution, so you can customize the refresh operation by creating triggers on materialized views.

Just two days ago, someone asked me on the blog, if you add a timestamp column to the materialized view and automatically maintain the column when the materialized view is updated. In fact, it's easy to do this by using triggers:

Sql> CREATE TABLE T (ID number PRIMARY KEY, NAME VARCHAR2 (30));

Table has been created.

Sql> INSERT into T SELECT rownum and tname from TAB;

25 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> CREATE materialized VIEW LOG on T;

The materialized view log has been created.

Sql> CREATE TABLE mv_t (ID number, NAME VARCHAR2 (), Time DATE DEFAULT sysdate);

Table has been created.

Sql> CREATE materialized VIEW mv_t on prebuilt TABLE REFRESH FAST

2 as SELECT * from T;

The materialized view was created.

Sql> ALTER session SET Nls_date_format = ' yyyy-mm-dd HH24:MI:SS ';

The session has changed.

Sql> SELECT * from T WHERE ID = 1;

ID NAME

---------- ------------------------------

1 T_ab

Sql> SELECT * from mv_t WHERE ID = 1;

No rows selected

Sql> EXEC Dbms_mview. REFRESH (' mv_t ', ' C ')

The PL/SQL process has completed successfully.

Sql> SELECT * from mv_t WHERE ID = 1;

ID NAME Time

---------- ------------------------------ -------------------

1 T_ab 2009-06-21 00:05:09

sql> UPDATE T SET NAME = ' A ' WHERE ID = 1;

1 rows have been updated.

Sql> COMMIT;

Submit completed.

Sql> EXEC Dbms_mview. REFRESH (' mv_t ', ' F ')

The PL/SQL process has completed successfully.

Sql> SELECT * from mv_t WHERE ID = 1;

ID NAME Time

---------- ------------------------------ -------------------

1 A 2009-06-21 00:05:09

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45472.htm

In this example, a materialized view of the on Prebuilt table type is created, where the base table of the materialized view has one more field than the primary table, and the default value is set for this field.

The materialized view automatically writes the default value in this field when it is fully refreshed, but if the base table is updated, the new timestamp field in the materialized view is not automatically updated when the refresh occurs.

In fact, the solution to this problem is very simple, an update trigger can be:

sql> CREATE OR REPLACE TRIGGER mv_t

2 before UPDATE on mv_t

3 for each ROW

4 BEGIN

5:new. Time: = Sysdate;

6 end;

7/

Trigger has been created

sql> UPDATE T SET NAME = ' B ' WHERE ID = 1;

1 rows have been updated.

Sql> COMMIT;

Submit completed.

Sql> SELECT * from mv_t WHERE ID = 1;

ID NAME Time

---------- ------------------------------ -------------------

1 A 2009-06-21 00:05:09

Sql> EXEC Dbms_mview. REFRESH (' mv_t ', ' F ')

The PL/SQL process has completed successfully.

Sql> SELECT * from mv_t WHERE ID = 1;

ID NAME Time

---------- ------------------------------ -------------------

1 B 2009-06-21 00:14:01

You only need to handle the update operation, because Delete does not cover this issue, and the insert operation does not require additional settings for the trigger because it has a default value set for the timestamp field of the table:

Sql> INSERT into T VALUES (num, ' C ');

1 lines have been created.

Sql> EXEC Dbms_mview. REFRESH (' mv_t ', ' F ')

The PL/SQL process has completed successfully.

Sql> SELECT * from mv_t WHERE ID = 26;

ID NAME Time

---------- ------------------------------ -------------------

C 2009-06-21 00:14:42