In-depth research on the materialized view (mview) of Oracle 4

In-depth research on the materialized view (mview) of Oracle 4
Now, when you move a base table in the first article, you will find that you cannot refresh the table quickly. You must refresh all the tables as follows:

SQL> ALTER TABLE t_mvlog move;

Table altered.

SQL> exec dbms_mview.refresh ('mv _ t_mvlog', 'quick ');
Begin dbms_mview.refresh ('mv _ t_mvlog', 'quick'); end;

*
Error at line 1:
ORA-12034: materialized view log on "anysql". "t_mvlog" younger than last refresh
ORA-06512: At "SYS. dbms_snapshot", line 803
ORA-06512: At "SYS. dbms_snapshot", line 860
ORA-06512: At "SYS. dbms_snapshot", line 841
ORA-06512: At line 1

Why is the failure? You can carefully analyze the process of refreshing the rowid-based mview log. When the base table is moved, the value of rowid changes, therefore, you cannot continue to support the update/delete operations of the primary table. next, we will redefine this materialized view based on the primary key. First, we will delete the existing materialized view and log, add a primary key to the table, and recreate the Materialized View:

SQL> ALTER TABLE t_mvlog modify col1 not null;

Table altered.

SQL> ALTER TABLE t_mvlog add primary key (col1 );

Table altered.

SQL> Create materialized view log on t_mvlog with primary key, sequence;

Materialized view log created.

SQL> Create materialized view mv_t_mvlog
2 refresh fast with primary key
3 As select rowid r_id, A. * From t_mvlog;

Materialized View created.

After moving the base table, you can perform incremental refresh.

SQL> ALTER TABLE t_mvlog move;

Table altered.

SQL> exec dbms_mview.refresh ('mv _ t_mvlog', 'quick ');

PL/SQL procedure successfully completed.

It is important to select the rowid-based or primary key-based materialized view logs.