mv(materialized view)的一點測試

mv在實現簡單的資料同步和資料遷移時非常方便。

是一種空間換時間的思想或者說是一種化整為零的處理思想。

[@more@]

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
中對應的記錄就不會被清除。