可更新的物化视图

快速刷新

可更新的物化视图


SQL> exec dbms_mview.refresh('MV_T','F');
BEGIN dbms_mview.refresh('MV_T','F'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "JEANRON"."T" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

create materialized view before materialized view log

exec dbms_mview.refresh('MV_T','C');

使用primary key

使用rowid.
如果使用rowid,需要考虑是否适合做快速刷新。如果有频繁的truncate之类的操作，rowid会发生变化。快速刷新就不适用了。
SQL> select rowid,object_id from t;


ROWID               OBJECT_ID
------------------ ----------
AAASx8AAGAAAi+NAAA        258
AAASx8AAGAAAi+NAAB        259
AAASx8AAGAAAi+NAAC        311
AAASx8AAGAAAi+NAAD        313
AAASx8AAGAAAi+NAAE        314
AAASx8AAGAAAi+NAAF        316
AAASx8AAGAAAi+NAAG        317
AAASx8AAGAAAi+NAAH        319
AAASx8AAGAAAi+NAAI        605

9 rows selected.

SQL> create table t2 as select *from t;
Table created.

SQL> truncate table t;
Table truncated.

SQL> insert into t select *from t2;
9 rows created.

SQL> commit;
Commit complete.

SQL> select rowid,object_id from t;
ROWID               OBJECT_ID
------------------ ----------
AAASyDAAGAAAi+aAAA        258
AAASyDAAGAAAi+aAAB        259
AAASyDAAGAAAi+aAAC        311
AAASyDAAGAAAi+aAAD        313
AAASyDAAGAAAi+aAAE        314
AAASyDAAGAAAi+aAAF        316
AAASyDAAGAAAi+aAAG        317
AAASyDAAGAAAi+aAAH        319
AAASyDAAGAAAi+aAAI        605

9 rows selected.