ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g materialized view物化视图示例

oracle10g materialized view物化视图示例

原创 Linux操作系统 作者:wisdomone1 时间:2013-07-18 12:13:17 0 删除 编辑
SQL> create table t_test(a int,b int);

Table created.

--构建物化视图基表必须有pk主键
SQL> create materialized view mv_t_test refresh fast as select a,b from t_test;
create materialized view mv_t_test refresh fast as select a,b from t_test
                                                                   *
ERROR at line 1:
ORA-12014: table 'T_TEST' does not contain a primary key constraint

--添加主键
SQL> alter table t_test add primary key(a)
  2  ;

Table altered.

--增量刷新(refresh fast)必须构建物化视图日志
SQL> create materialized view mv_t_test refresh fast as select a,b from t_test;
create materialized view mv_t_test refresh fast as select a,b from t_test
                                                                   *
ERROR at line 1:
ORA-23413: table "SCOTT"."T_TEST" does not have a materialized view log


SQL> create materialized view mv_t_test refresh force as select a,b from t_test;

Materialized view created.

---查看物化视图相关信息
select owner,mview_name,updatable,update_log from user_mviews

OWNER                          MVIEW_NAME                     U UPDATE_LOG
------------------------------ ------------------------------ - ------------------------------
SCOTT                          MV_T_TEST                      N

SQL> 


SQL> select mview_name,rewrite_enabled,rewrite_capability from user_mviews;

MVIEW_NAME                     R REWRITE_C
------------------------------ - ---------
MV_T_TEST                      N GENERAL


SQL> select mview_name,refresh_mode,refresh_method,build_mode from user_mviews;

MVIEW_NAME                     REFRES REFRESH_ BUILD_MOD
------------------------------ ------ -------- ---------
MV_T_TEST                      DEMAND FORCE    IMMEDIATE


SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DML                COMPLETE 2013-07-18 10:55:11


SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DML                COMPLETE 2013-07-18 11:05:15


--增量刷新报错,第二个参数指定refresh mode,值为:f表示fast;c表示complete;?表示force;p表示基于分区刷新pct
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-23413: table "SCOTT"."T_TEST" does not have a materialized view log
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


SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('mv_t_test','?');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('mv_t_test','x');
BEGIN dbms_mview.refresh('mv_t_test','x'); END;

*
ERROR at line 1:
ORA-01760: illegal argument for function
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


SQL> exec dbms_mview.refresh('mv_t_test','p');
BEGIN dbms_mview.refresh('mv_t_test','p'); END;

*
ERROR at line 1:
ORA-12047: PCT FAST REFRESH cannot be used for materialized view "SCOTT"."MV_T_TEST"
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


SQL> 
--构建物化视图日志
SQL> create materialized view log on t_test;

Materialized view log created.

SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" 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

SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

no rows selected

--构建物化视图日志后先要全量刷新,才可以进行增量刷新
SQL> !oerr ora 12034
12034, 0000, "materialized view log on \"%s\".\"%s\" younger than last refresh"
// *Cause:  The materialized view log was younger than the last refresh.
// *Action: A complete refresh is required before the next fast refresh.
//


SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

no rows selected

--全量刷新后增量刷新即可
SQL> exec dbms_mview.refresh('mv_t_test','f');

PL/SQL procedure successfully completed.


SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DML                FAST     2013-07-18 11:19:49

SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

--构建物化视图日志后自动进行增量刷新
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DML                FAST     2013-07-18 11:22:26

--插入记录不提交
SQL> insert into t_test select level,level+3 from dual connect by level<=20;

20 rows created.

--未提交前在物化视图日志插入记录
select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
         1 4000-01-01 00:00:00 I N FE
         2 4000-01-01 00:00:00 I N FE
         3 4000-01-01 00:00:00 I N FE
         4 4000-01-01 00:00:00 I N FE
         5 4000-01-01 00:00:00 I N FE
         6 4000-01-01 00:00:00 I N FE
         7 4000-01-01 00:00:00 I N FE
         8 4000-01-01 00:00:00 I N FE
         9 4000-01-01 00:00:00 I N FE
        10 4000-01-01 00:00:00 I N FE
        11 4000-01-01 00:00:00 I N FE

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
        12 4000-01-01 00:00:00 I N FE
        13 4000-01-01 00:00:00 I N FE
        14 4000-01-01 00:00:00 I N FE
        15 4000-01-01 00:00:00 I N FE
        16 4000-01-01 00:00:00 I N FE
        17 4000-01-01 00:00:00 I N FE
        18 4000-01-01 00:00:00 I N FE
        19 4000-01-01 00:00:00 I N FE
        20 4000-01-01 00:00:00 I N FE

20 rows selected.

SQL> commit;

Commit complete.

--提交后仍保存物化视图日志
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
         1 4000-01-01 00:00:00 I N FE
         2 4000-01-01 00:00:00 I N FE
         3 4000-01-01 00:00:00 I N FE
         4 4000-01-01 00:00:00 I N FE
         5 4000-01-01 00:00:00 I N FE
         6 4000-01-01 00:00:00 I N FE
         7 4000-01-01 00:00:00 I N FE
         8 4000-01-01 00:00:00 I N FE
         9 4000-01-01 00:00:00 I N FE
        10 4000-01-01 00:00:00 I N FE
        11 4000-01-01 00:00:00 I N FE

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
        12 4000-01-01 00:00:00 I N FE
        13 4000-01-01 00:00:00 I N FE
        14 4000-01-01 00:00:00 I N FE
        15 4000-01-01 00:00:00 I N FE
        16 4000-01-01 00:00:00 I N FE
        17 4000-01-01 00:00:00 I N FE
        18 4000-01-01 00:00:00 I N FE
        19 4000-01-01 00:00:00 I N FE
        20 4000-01-01 00:00:00 I N FE

20 rows selected.

--刷新物化视图
SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

--刷新物化视图则有
SQL> select * from mv_t_test;

         A          B
---------- ----------
         1          4
         6          9
        11         14
        13         16
         2          5
        14         17
        20         23
         4          7
         5          8
         8         11
        17         20

         A          B
---------- ----------
         3          6
         7         10
        18         21
         9         12
        10         13
        12         15
        15         18
        16         19
        19         22

20 rows selected.

--刷新物化视图后则删除物化视图日志记录
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

no rows selected


--更新不提交
SQL> update t_test set b=44 where a=1;

1 row updated.


--通过物化视图日志列可区别源表的不同操作类型,
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
         1 4000-01-01 00:00:00 U U 04
         

SQL> rollback;

Rollback complete.

--回滚也清除物化视图日志内容
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

no rows selected


SQL> delete from t_test where a=1;

1 row deleted.

---删除不提交在物化视图日志也会记录对应的内容
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
         1 4000-01-01 00:00:00 D O 00
         
         
---删除物化视图        
SQL> drop materialized view  mv_t_test;

Materialized view dropped.

--构建聚合物化视图
SQL> create materialized view mv_t_test enable query rewrite as select a,count(b) from t_test group by a;

Materialized view created.

--可知聚合物化视图首次是全量刷新,且增量刷新是有限制条件的
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DIRLOAD_LIMITEDDML COMPLETE 2013-07-18 11:42:44

SQL> select * from t_test;

         A          B
---------- ----------
         2          5
         3          6
         4          7
         5          8
         6          9
         7         10
         8         11
         9         12
        10         13
        11         14
        12         15

         A          B
---------- ----------
        13         16
        14         17
        15         18
        16         19
        17         20
        18         21
        19         22
        20         23

19 rows selected.

--物化视图已有值
SQL> select * from mv_t_test;

         A   COUNT(B)
---------- ----------
         6          1
        11          1
        13          1
         2          1
        14          1
        20          1
         4          1
         5          1
         8          1
        17          1
         3          1

         A   COUNT(B)
---------- ----------
         7          1
        18          1
         9          1
        10          1
        12          1
        15          1
        16          1
        19          1

19 rows selected.

--插入一条记录
SQL> insert into t_test values(21,28);

1 row created.

--刷新物化视图
SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DIRLOAD_LIMITEDDML COMPLETE 2013-07-18 11:48:04


--即使未提交刷新物化视图也会同步新数据
SQL> select * from mv_t_test;

         A   COUNT(B)
---------- ----------
         6          1
        11          1
        13          1
         2          1
        14          1
        20          1
        21          1
         4          1
         5          1
         8          1
        17          1

         A   COUNT(B)
---------- ----------
         3          1
         7          1
        18          1
         9          1
        10          1
        12          1
        15          1
        16          1
        19          1

20 rows selected.




--增量刷新报错
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T_TEST"
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




SQL> !oerr ora 12032
12032, 0000, "cannot use rowid column from materialized view log on \"%s\".\"%s\""
// *Cause:  The materialized view log either does not have ROWID columns
//          logged, or the timestamp associated with the ROWID columns is
//          more recent than the last refresh time.
// *Action: A complete refresh is required before the next fast refresh.
//          Add ROWID columns to the materialized view log, if required.
//

SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

--完全刷新后增量刷新依旧报错
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T_TEST"
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

--添加rowid到物化视图日志
SQL> alter materialized view log force on t_test add rowid;

Materialized view log altered.

---物化视图日志多了一个列m_row$$
SQL> desc mlog$_t_test;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 A                                                                                                                          NUMBER
 SNAPTIME$$                                                                                                                 DATE
 DMLTYPE$$                                                                                                                  VARCHAR2(1)
 OLD_NEW$$                                                                                                                  VARCHAR2(1)
 CHANGE_VECTOR$$                                                                                                            RAW(255)
 M_ROW$$   
 
 
 SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" 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


SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-32401: materialized view log on "SCOTT"."T_TEST" does not have new values
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


--添加新值到物化视图日志
SQL> alter materialized view log force on t_test including new values;

Materialized view log altered.


SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" 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


SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

--添加新值到物化视图日志后仍报错
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
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


SQL> !oerr ora 12033
12033, 0000, "cannot use filter columns from materialized view log on \"%s\".\"%s\""
// *Cause:  The materialized view log either did not have filter columns
//          logged, or the timestamp associated with the filter columns was
//          more recent than the last refresh time.
// *Action: A complete refresh is required before the next fast refresh.
//          Add filter columns to the materialized view log, if required.
//


SQL> alter materialized view log force on t_test add rowid(a);

Materialized view log altered.


SQL> alter materialized view log force on t_test add rowid(a);

Materialized view log altered.

--添加过滤列仍报错
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
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

--添加过滤列
SQL> alter materialized view log force on t_test add (b);

Materialized view log altered.

SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
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


--添加过滤列然后全量刷新之后增量即可成功
SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('mv_t_test','f');

PL/SQL procedure successfully completed.

--可知上次是增量刷新
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DIRLOAD_LIMITEDDML FAST     2013-07-18 12:08:10


SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

--不加第二个参数也是增量刷新
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DIRLOAD_LIMITEDDML FAST     2013-07-18 12:11:15

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-766609/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2150
  • 访问量
    11857419