基于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.

–实现