基於ROWID更新的物化檢視測試

–建立基表

create table hr.t1 (  
id number(10),    
name1 varchar2(10),    
name2 varchar2(10),    
name3 varchar2(10)    
);

insert into hr.t1 values (1,’a',’b',’c');

insert into hr.t1 values (1,’a',’b',’c');

insert into hr.t1 values (1,’a',’b',’c');

insert into hr.t1 values (2,’a',’b',’c');

insert into hr.t1 values (2,’a',’b',’c');

insert into hr.t1 values (2,’a',’b',’c');

insert into hr.t1 values (3,’a',’b',’c');

insert into hr.t1 values (1,’a',’b',’c');

insert into hr.t1 values (1,’a',’b',’c');

commit;

SQL> select rowid, id, name1, name2, name3 from hr.t1;

ROWID                      ID NAME1      NAME2      NAME3  
—————— ———- ———- ———- ———-    
AAANxRAAHAAAESsAAA          1 a          b          c    
AAANxRAAHAAAESsAAB          1 a          b          c    
AAANxRAAHAAAESsAAC          1 a          b          c    
AAANxRAAHAAAESsAAD          2 a          b          c    
AAANxRAAHAAAESsAAE          2 a          b          c    
AAANxRAAHAAAESsAAF          2 a          b          c    
AAANxRAAHAAAESsAAG          3 a          b          c    
AAANxRAAHAAAESsAAH          1 a          b          c    
AAANxRAAHAAAESsAAI          1 a          b          c

9 rows selected.

–建立物化檢視

CREATE MATERIALIZED VIEW hr.t1_mv  
BUILD IMMEDIATE    
REFRESH COMPLETE    
ENABLE QUERY REWRITE AS    
SELECT  id, name1, name2, name3    
FROM t1;

drop materialized view t1_mv;

–更新id=1的第三條記錄的值  
update hr.t1 set id=4 where rowid=’AAANxRAAHAAAESsAAC’;

exec DBMS_MVIEW.REFRESH(‘HR.T1_MV’, ‘C’, ”, TRUE, FALSE, 0,0,0, FALSE);

SQL> select * from t1_mv;

        ID NAME1      NAME2      NAME3  
———- ———- ———- ———-    
         1 a          b          c    
         1 a          b          c    
         4 a          b          c    
         2 a          b          c    
         2 a          b          c    
         2 a          b          c    
         3 a          b          c    
         1 a          b          c    
         1 a          b          c

–更新成功

–說明物化檢視在基於ROWID更新基表之後，透過全量重新整理是可以更新物化檢視

VARIABLE task_cust_mv VARCHAR2(30);  
VARIABLE create_mv_ddl VARCHAR2(4000);    
EXECUTE :task_cust_mv := ‘t1_mv2′;

EXECUTE :create_mv_ddl := ‘ CREATE MATERIALIZED VIEW t1_mv2 REFRESH FAST DISABLE QUERY REWRITE AS SELECT id, name1, name2, name3 FROM t1′;

EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);

ERROR at line 1:  
ORA-13600: error encountered in Advisor    
QSM-03113: Cannot tune the MATERIALIZED VIEW statement    
QSM-02180: no primary key constraint in the master table    
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86    
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202    
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1232    
ORA-06512: at "SYS.DBMS_ADVISOR", line 753    
ORA-06512: at line 1

–無法支援快速重新整理，如果建立主鍵，就失去基於ROWID更新的必要性

建立基於ROWID的物化檢視，並測試快速更新方式

VARIABLE task_cust_mv VARCHAR2(30);  
VARIABLE create_mv_ddl VARCHAR2(4000);    
EXECUTE :task_cust_mv := ‘t1_mv2′;

EXECUTE :create_mv_ddl := ‘CREATE MATERIALIZED VIEW t1_mv2 BUILD IMMEDIATE REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT  id, name1, name2, name3 FROM t1′;

EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);

SQL> set long 999999  
SQL> SELECT STATEMENT FROM USER_TUNE_MVIEW;

CREATE MATERIALIZED VIEW LOG ON "HR"."T1" WITH ROWID;  
ALTER MATERIALIZED VIEW LOG FORCE ON "HR"."T1" ADD ROWID;    
CREATE MATERIALIZED VIEW HR.T1_MV2    
BUILD IMMEDIATE    
REFRESH FAST WITH ROWID    
ENABLE QUERY REWRITE AS    
SELECT  id, name1, name2, name3    
FROM t1;

DROP MATERIALIZED VIEW HR.T1_MV2;

SQL> select rowid, id, name1, name2, name3 from t1;

ROWID                      ID NAME1      NAME2      NAME3  
—————— ———- ———- ———- ———-    
AAANxRAAHAAAESsAAA          1 a          b          c    
AAANxRAAHAAAESsAAB          1 a          b          c    
AAANxRAAHAAAESsAAC          4 a          b          c    
AAANxRAAHAAAESsAAD          2 a          b          c    
AAANxRAAHAAAESsAAE          2 a          b          c    
AAANxRAAHAAAESsAAF          2 a          b          c    
AAANxRAAHAAAESsAAG          3 a          b          c    
AAANxRAAHAAAESsAAH          1 a          b          c    
AAANxRAAHAAAESsAAI          1 a          b          c

–修改id=2 的第二行記錄

SQL> update t1 set name1=’z’ where rowid=’AAANxRAAHAAAESsAAE’;

1 row updated.

SQL> commit;

Commit complete.

SQL> select rowid, id, name1, name2, name3 from t1;

ROWID                      ID NAME1      NAME2      NAME3  
—————— ———- ———- ———- ———-    
AAANxRAAHAAAESsAAA          1 a          b          c    
AAANxRAAHAAAESsAAB          1 a          b          c    
AAANxRAAHAAAESsAAC          4 a          b          c    
AAANxRAAHAAAESsAAD          2 a          b          c    
AAANxRAAHAAAESsAAE          2 z          b          c    
AAANxRAAHAAAESsAAF          2 a          b          c    
AAANxRAAHAAAESsAAG          3 a          b          c    
AAANxRAAHAAAESsAAH          1 a          b          c    
AAANxRAAHAAAESsAAI          1 a          b          c

9 rows selected.

SQL> select * from t1_mv2;

        ID NAME1      NAME2      NAME3  
———- ———- ———- ———-    
         1 a          b          c    
         1 a          b          c    
         4 a          b          c    
         2 a          b          c    
         2 a          b          c    
         2 a          b          c    
         3 a          b          c    
         1 a          b          c    
         1 a          b          c

9 rows selected.

exec DBMS_MVIEW.REFRESH(‘HR.T1_MV2′, ‘F’, ”, TRUE, FALSE, 0,0,0, FALSE);

–驗證MV的特性

SQL> @?/rdbms/admin/utlxmv.sql

SQL> EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW (‘HR.T1_MV2′);

PL/SQL procedure successfully completed.

SQL>  SELECT capability_name,  
  2  possible    
  3  from mv_capabilities_table    
  4  order by seq;

CAPABILITY_NAME                P  
—————————— –    
PCT                            N    
REFRESH_COMPLETE               Y    
REFRESH_FAST                   Y    
REWRITE                        Y    
PCT_TABLE                      N    
REFRESH_FAST_AFTER_INSERT      Y    
REFRESH_FAST_AFTER_ONETAB_DML  Y    
REFRESH_FAST_AFTER_ANY_DML     Y    
REFRESH_FAST_PCT               N    
REWRITE_FULL_TEXT_MATCH        Y    
REWRITE_PARTIAL_TEXT_MATCH     Y

CAPABILITY_NAME                P  
—————————— –    
REWRITE_GENERAL                Y    
REWRITE_PCT                    N    
PCT_TABLE_REWRITE              N

14 rows selected.

SQL> select * from t1_mv2;

        ID NAME1      NAME2      NAME3  
———- ———- ———- ———-    
         1 a          b          c    
         1 a          b          c    
         4 a          b          c    
         2 a          b          c    
         2 z          b          c    
         2 a          b          c    
         3 a          b          c    
         1 a          b          c    
         1 a          b          c

9 rows selected.

–實現