Materialized View 簡單實踐
??? 為了對物化視圖的一些性能進行測試，做了幾個簡單的實驗。都是一些最最進本的操作，主要是為了確認物化視圖在各種設置下的真實反映。例如是否可以使用FAST刷新，以及on prebuilt table子句對表和物化視圖的不同影響，下面看一下例子：
1、首先在實例A中創建表T8：
?
create table t8 (i int primary key,a number,n varchar2(100));
insert into t8 values(1,10,'aaaaaaaaaaa');
insert into t8 values(2,20,'bbbbbbbbbbb');
insert into t8 values(3,30,'ccccccccccc');
insert into t8 values(4,40,'ddddddddddd');
insert into t8 values(5,50,'eeeeeeeeeee');
insert into t8 values(6,60,'fffffffffff');
insert into t8 values(7,70,'ggggggggggg');
commit;
?
?
2、在實例B中創建A的DBLINK temp_link：
?
create public database link temp_link
connect to wxq identified by wxq
using 'SID';
?
?
3、在實例B中創建物化視圖wxq_mv：
?
create materialized view wxq_mv
tablespace wxq_tbs
build deferred? --延遲刷新不立即刷新,immediate為立即刷新
refresh force?? --如果可以快速刷新則進行快速刷新，否則完全刷新
on demand?????? --按照指定方式刷新，遠程復制不能使用commit
start with to_date('10-09-2008 16:45:10', 'dd-mm-yyyy hh24:mi:ss')
next sysdate + 1
with primary key
as
注1：這個過程遇到一個錯誤，在next子句后面加了注釋后編譯報錯，暈，搞了N久才發現。
注2：發現在MV自動自制JOB后到了時間沒有觸發，查了下資料，是JOB_QUEUE_INTERVA參數設置為0，alter system set job_queue_processes=10后可以自動運行。
注3：手動運行job——execute dbms_job.run(21)
?
?
4、檢查數據：
?
執行job刷新視圖后查詢數據：
?
SQL> execute dbms_job.run(26);
PL/SQL procedure successfully completed
?
SQL> column i format a5
SQL> column a format a5
SQL> column n format a15
SQL> column mydate format a20
?
SQL> select * from wxq_mv;
?
??? I???? A N?????????????? MYDATE
----- ----- --------------- --------------------
??? 1??? 10 aaaaaaaaaaa???? 2008-9-10 17:41:38
??? 2??? 20 bbbbbbbbbbb???? 2008-9-10 17:41:38
??? 3??? 30 ccccccccccc???? 2008-9-10 17:41:38
??? 4??? 40 ddddddddddd???? 2008-9-10 17:41:38
??? 5??? 50 eeeeeeeeeee???? 2008-9-10 17:41:38
??? 6??? 60 fffffffffff???? 2008-9-10 17:41:38
??? 7??? 70 ggggggggggg???? 2008-9-10 17:41:38
?
7 rows selected
?
?
在A實例中修改數據：
?
SQL> update t8 set a=100 where i=1;
?
1 row updated
?
SQL> commit;
?
Commit complete
?
?
再從B實例中查詢數據：
?
SQL> execute dbms_job.run(26);
?
PL/SQL procedure successfully completed
?
SQL> select * from wxq_mv;
?
??? I???? A N?????????????? MYDATE
----- ----- --------------- --------------------
??? 1?? 100 aaaaaaaaaaa???? 2008-9-10 17:47:20
??? 2??? 20 bbbbbbbbbbb???? 2008-9-10 17:47:20
??? 3??? 30 ccccccccccc???? 2008-9-10 17:47:20
??? 4??? 40 ddddddddddd???? 2008-9-10 17:47:20
??? 5??? 50 eeeeeeeeeee???? 2008-9-10 17:47:20
??? 6??? 60 fffffffffff???? 2008-9-10 17:47:20
??? 7??? 70 ggggggggggg???? 2008-9-10 17:47:20
?
7 rows selected
?
從時間可以看到，是全部刷新的
?
?
5、創建log后檢查數據：
?
在A實例中創建materialized view log
?
SQL> create materialized view log on t8 with rowid;
?
Materialized view log created
?
在B實例中建立的物化視圖：
?
SQL> create materialized view wxq_mv2
? 2? tablespace wxq_tbs
? 3? build deferred? --延遲刷新不立即刷新,immediate為立即刷新
? 4? refresh fast??? --使用增量刷新
? 5? on demand?????? --按照指定方式刷新
? 6? start with to_date('10-09-2008 16:45:10', 'dd-mm-yyyy hh24:mi:ss')
? 7? next sysdate + 1
? 8? with rowid
? 9? as
10? select * from
t8@temp_link
t;
?
Materialized view created
?
注：這里不能加入sysdate字段了，因為遠程快速刷新只能是簡單的表復制
??? 只有創建materialized view log 后才可以refresh fast
?
?
6、查看已經創建的materialized view：
?
SQL> select query from dba_mviews where mview_name='WXQ_MV2';
?
QUERY
--------------------------------------------------------------------------------
SELECT "T"."I" "I","T"."A" "A","T"."N" "N" FROM
"T8"@TEMP_LINK.SINATAY.COM
"T"
?
?
發現已經把*自動轉化為每個列名，這跟view是一樣的
所以，當遠程數據庫A增加列時，對本地B的mv不產生影響，B將忽略新增的列
但是當A改變原有的列名，或刪除了B的mv引用的列名時，mv更新時就會報錯
?
?
7、物化視圖將新建一個表、一個job：
?
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='WXQ_MV2';
?
OWNER????????????????????????? TABLE_NAME???????????????????? TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
WANGXIAOQI???????????????????? WXQ_MV2??????????????????????? WXQ_TBS
?
SQL> desc WXQ_MV2
Name Type????????? Nullable Default Comments
---- ------------- -------- ------- --------
I??? INTEGER????????????????????????????????
A??? NUMBER??????? Y????????????????????????
N??? VARCHAR2(100) Y???
?
SQL> select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
?
JOB?? LOG_USER??????? LAST_DATE??? LAST_SEC??? NEXT_DATE?? NEXT_SEC? INTERVAL???? WHAT
----- --------------- ------------ ----------- ----------- --------- ------------ ----------------------------------------------
29??? WANGXIAOQI????? 2008-9-17??? 09:48:10??? 2008-9-17?? 14:04:12? sysdate + 1? dbms_refresh.refresh('"WANGXIAOQI"."WXQ_MV2"');
每個mv都會對應一個實體表和一個job
?
?
8、查看是否會創建主鍵：
?
當原始表T8沒有主鍵時，MV只能創建為with rowid模式
當然生成的mv table也是沒有主鍵的
?
當原始表T8含有主鍵時，必須要重新生成log，然后再創建mv時必須使用with primary key
?
SQL> alter table t8 add constraint t8_key primary key(i);
?
Table altered
?
SQL> drop materialized view log on t8 ;
?
Materialized view log dropped
?
SQL> create materialized view log on t8 ;
?
Materialized view log created
?
SQL> select constraint_name,table_name,status from user_constraints where table_name='T8';
?
CONSTRAINT_NAME??????????????? TABLE_NAME???????????????????? STATUS
------------------------------ ------------------------------ --------
T8_KEY???????????????????????? T8???????????????????????????? ENABLED
?
?
然后在B創建MV：
?
SQL> create materialized view wxq_mv2
? 2? tablespace wxq_tbs
? 3? build deferred? --延遲刷新不立即刷新,immediate為立即刷新
? 4? refresh fast??? --使用增量刷新
? 5? on demand?????? --按照指定方式刷新
? 6? start with to_date('10-09-2008 16:45:10', 'dd-mm-yyyy hh24:mi:ss')
? 7? next sysdate + 1
? 8? with primary key?? --使用rowid時報錯
? 9? as
10? select * from
t8@temp_link
t;
?
Materialized view created
?
SQL> select constraint_name,table_name,status from dba_constraints where table_name='WXQ_MV3';
?
CONSTRAINT_NAME??????????????? TABLE_NAME???????????????????? STATUS
------------------------------ ------------------------------ --------
T8_KEY???????????????????????? WXQ_MV3??????????????????????? ENABLED
?
生成的表中已經含有主鍵和索引。
?
?
9、關于使用on prebuilt table在已有表上建立MV的操作和應用：
?
先創建一個新表T2：
?
SQL> create table t2 (a int primary key,b varchar2(10));
?
Table created
?
創建對應的MV：
?
SQL> create materialized view wxq_mv as select * from t2;
?
Materialized view created
?
已經生成了一個table和一個MV：
?
SQL> select object_name,object_type from user_objects where object_name='WXQ_MV';
?
OBJECT_NAME????????????????????????????????????????????????????????????????????? OBJECT_TYPE
-------------------------------------------------------------------------------- ------------------
WXQ_MV?????????????????????????????????????????????????????????????????????????? TABLE
WXQ_MV?????????????????????????????????????????????????????????????????????????? MATERIALIZED VIEW
?
刪除MV：
SQL> drop materialized view wxq_mv;
?
Materialized view dropped
?
?
發現table和MV都已經被刪除：
?
SQL> select object_name,object_type from user_objects where object_name='WXQ_MV';
?
OBJECT_NAME????????????????????????????????????????????????????????????????????? OBJECT_TYPE
-------------------------------------------------------------------------------- ------------------
?
?
下面使用on prebuilt table來創建MV：
先建一個新表T3用來存放MV
?
SQL> create table t3 as select * from t2;
?
Table created
在T3上建立MV：
?
SQL> create materialized view T3 on prebuilt table as select * from t2;
?
Materialized view created
?
?
查看新建的MV，還是有table：
?
SQL> select object_name,object_type from user_objects where object_name='T3';
?
OBJECT_NAME????????????????????????????????????????????????????????????????????? OBJECT_TYPE
-------------------------------------------------------------------------------- ------------------
T3?????????????????????????????????????????????????????????????????????????????? TABLE
T3?????????????????????????????????????????????????????????????????????????????? MATERIALIZED VIEW
?
再刪除MV：
?
SQL> drop materialized view t3;
Materialized view dropped
?
發現T3任然保留，其數據也保留到最后更新狀態：
?
SQL> select object_name,object_type from user_objects where object_name='T3';
?
OBJECT_NAME????????????????????????????????????????????????????????????????????? OBJECT_TYPE
-------------------------------------------------------------------------------- ------------------
T3?????????????????????????????????????????????????????????????????????????????? TABLE
?
?
應用說明：可以先使用表基礎上的MV來進行增量更新，當需要切換數據庫數據時，斷開源數據庫，然后刪除MV即可
??????? 缺點是MV針對的是對象，如果對象很多的話，建立起來比較麻煩。
?
?