–创建基表

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.

–实现




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

only 1 comment untill now

  1. murrin@consternation.wrappin” rel=”nofollow”>.…

    tnx!!…

Add your comment now

无觅相关文章插件