mv(materialized view)的一点测试

mv在实现简单的数据同步和数据迁移时非常方便。

是一种空间换时间的思想或者说是一种化整为零的处理思想。

 

SQL> show user
USER is "TEST"
--测试一下fast刷新，fast刷新（是指增量刷新）一定要创建materialzed view log

--==========================================
SQL> create MATERIALIZED view log on t tablespace users;

Materialized view log created.

SQL> create materialized view mv_t tablespace users refresh fast as select * from t ;

Materialized view created.

SQL> select * from mv_t;

no rows selected

SQL> select * from t;

no rows selected

SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
NAME VARCHAR2(10)

SQL> insert into t values(1,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

ID NAME
---------- ----------
1 a

SQL> select * from mv_t;

no rows selected

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

PL/SQL procedure successfully completed.

SQL> select * from mv_t;

ID NAME
---------- ----------
1 a

SQL> insert into t values(2,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv_t;

ID NAME
---------- ----------
1 a

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

PL/SQL procedure successfully completed.

SQL> select * from mv_t;

ID NAME
---------- ----------
1 a
2 a

SQL> insert into t values(3,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv_t;

ID NAME
---------- ----------
1 a
2 a

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

PL/SQL procedure successfully completed.

SQL> select * from mv_t;

ID NAME
---------- ----------
1 a
2 a
3 a
--=====================================================
--测试一下on commit刷新方式
SQL> create materialized view mv_t2 tablespace users refresh fast on commit as select * from t ;

Materialized view created.

SQL> select * from mv_t2;

ID NAME
---------- ----------
1 a
2 a
3 a
4 a
5 a
6 a

6 rows selected.

SQL> insert into t values(7,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv_t2;

ID NAME
---------- ----------
1 a
2 a
3 a
4 a
5 a
6 a
7 a

7 rows selected.

SQL>
--=====================================
--测试一下oracle对实体化视图日志的自动维护
SQL> desc mlog$_t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)

SQL> col CHANGE_VECTOR$$ format a10
SQL> select *from mlog$_t;

no rows selected

SQL> insert into t values(9,'a');

1 row created.
--插入数据之后mlog$_t里面记录了日志
SQL> select *from mlog$_t;

ID SNAPTIME$$ D O CHANGE_VEC
---------- ------------------- - - ----------
9 4000/01/01 00:00:00 I N FE

SQL> select * from mv_t2;

ID NAME
---------- ----------
1 a
2 a
3 a
4 a
5 a
6 a
7 a
8 a

8 rows selected.

SQL> commit;

Commit complete.

SQL> select * from mv_t2;

ID NAME
---------- ----------
1 a
2 a
3 a
4 a
5 a
6 a
7 a
8 a
9 a

9 rows selected.

SQL> select * from mv_t;

ID NAME
---------- ----------
1 a
2 a
3 a
4 a
6 a
5 a
8 a
7 a

8 rows selected.

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

PL/SQL procedure successfully completed.

SQL> select * from mv_t;

ID NAME
---------- ----------
1 a
2 a
3 a
4 a
6 a
5 a
8 a
7 a
9 a

9 rows selected.

SQL> select *from mlog$_t;

no rows selected

SQL>
--很显然mv_t刷新之后oracle自动清除了mlog$_t里面的记录。也就是说实体化视图日志的内容
默认oracle是自动维护的。一个基本只能创建一个materialized view log，基于这张基表可以创建
若干materialized view，只要一个materialized view没有刷新完毕，materialized view log
中对应的记录就不会被清除。