–创建基表
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.
–实现