用Trigger為MView手動(dòng)增加時(shí)間戳
?
?
??? 很多人都遇到過(guò)這樣的問(wèn)題：源數(shù)據(jù)庫(kù)里的表沒(méi)有時(shí)間戳，或者有時(shí)間戳的字段但沒(méi)有嚴(yán)格執(zhí)行，而歷史數(shù)據(jù)又可能會(huì)經(jīng)常性得被修改。這對(duì)于ETL數(shù)據(jù)抽取來(lái)說(shuō)將是一個(gè)很大的問(wèn)題，特別是對(duì)于不能動(dòng)源庫(kù)的情況下，很難解決這個(gè)問(wèn)題。最近又因?yàn)檫@個(gè)問(wèn)題，考慮了很久，最后打算在MView同步數(shù)據(jù)的基礎(chǔ)上，通過(guò)Trigger來(lái)手動(dòng)增加時(shí)間戳。
?
??? 考慮對(duì)MView操作的原因是因?yàn)楝F(xiàn)有的數(shù)據(jù)同步就是用MView做的，據(jù)說(shuō)用Stream同步數(shù)據(jù)也可以用類似的辦法添加時(shí)間戳的，這個(gè)相關(guān)的問(wèn)題以后再研究，講一下MView下的操作。
?
??? 首先MView有一些天然的限制，因?yàn)樽畛跷铱紤]的是在建MView的時(shí)候就添加一個(gè)SYSDATE的字段，這樣每次同步就會(huì)自動(dòng)將同步時(shí)的sysdate也加入到新的物化視圖中，但是這個(gè)嘗試失敗了，因?yàn)樵隽?Fast)更新不支持復(fù)雜表結(jié)構(gòu)，而Oracle認(rèn)為新增字段的表都屬于復(fù)雜表，會(huì)報(bào)錯(cuò)如下：
??? ORA-12015: cannot create a fast refresh materialized view from a complex query
??? 然后又希望能夠通過(guò)物化視圖的on commit刷新模式對(duì)表進(jìn)行刷新，這樣可以直接查詢ora_rowscn來(lái)判斷某行的更新時(shí)間，但是很遺憾，on commit的刷新模式只針對(duì)同庫(kù)下的更新，不支持遠(yuǎn)程同步，所以沒(méi)辦法只能用Trigger來(lái)自己手工增加時(shí)間戳。具體的實(shí)驗(yàn)步驟如下：
?
?
?
1、在源庫(kù)中建立表t1，并添加mv log：
?
SQL> create table t1(a int primary key,b int);
?
Table created.
?
SQL> insert into t1 values(1,2);
?
1 row created.
?
SQL> insert into t1 values(3,4);
?
1 row created.
?
SQL> commit;
?
Commit complete.
?
SQL> select * from t1;
?
???????? A????????? B
---------- ----------
???????? 1????????? 2
???????? 3????????? 4
?
SQL> create materialized view log on t1 with primary key;
?
Materialized view log created.
?
?
2、在新
庫(kù)上添加源庫(kù)的DB LINK：
?
SQL> create database link wxq_db
? 2? connect to wangxiaoqi identified by wangxiaoqi
? 3? using '(DESCRIPTION =
? 4????? (ADDRESS_LIST =
? 5??????? (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.1.127)(PORT = 1521))
? 6????? )
? 7????? (CONNECT_DATA =
? 8??????? (SERVICE_NAME = devWXQowb)
? 9?????? )
10????? )';
Database link created.
?
SQL> select * from
t1@wxq_db
;
?
???????????? A????????????? B
-------------- --------------
???????????? 1????????????? 2
???????????? 3????????????? 4
?
?
3、在新庫(kù)創(chuàng)建物化視圖，設(shè)置增量刷新
?
SQL> create materialized view wxq_t1
? 2? build deferred
? 3? refresh fast
? 4? on demand
? 5? with primary key
? 6? as
? 7? select * from
t1@wxq_db
;
Materialized view created.
?
SQL> select * from wxq_t1;
?
no rows selected
?
--第一次必須要全量刷新
?
SQL> exec dbms_mview.refresh('wxq_t1','complete');
?
PL/SQL procedure successfully completed.
?
SQL> select * from wxq_t1;
?
???????? A????????? B
---------- ----------
???????? 1????????? 2
???????? 3????????? 4
?
?
4、在新庫(kù)創(chuàng)建關(guān)于表T1的Trigger：
?
SQL> create table t1_log(a int,c_flag varchar2(2),modifydate date);
?
Table created.
SQL> create or replace trigger t1_tgr after update or insert or delete on wxq_t1
? 2???? referencing old as oldrow new as newrow for each row
? 3? begin
? 4???? if inserting then
? 5???? insert into t1_log values(:newrow.a,'I',sysdate);
? 6???? end if;
? 7???? if updating then
? 8???? insert into t1_log values(:oldrow.a,'U',sysdate);
? 9???? end if;
10???? if deleting then
11???? insert into t1_log values(:oldrow.a,'D',sysdate);
12???? end if;
13? end;
14? /
Trigger created.
?
?
5、來(lái)測(cè)試一下在源庫(kù)中進(jìn)行插入、刪除、修改操作
?
SQL> select * from t1;
?
???????? A????????? B
---------- ----------
???????? 1????????? 2
???????? 3????????? 4
?
SQL> insert into t1 values(10,20);
?
1 row created.
?
SQL> update t1 set b=40 where a=3;
?
1 row updated.
SQL> delete from t1 where a=1;
?
1 row deleted.
SQL> commit;
?
Commit complete.
?
SQL> select * from t1;
?
???????? A????????? B
---------- ----------
??????? 10???????? 20
???????? 3???????? 40
?
SQL> column change_vector$$ format a20
SQL> select * from mlog$_t1;
?
???????? A SNAPTIME$$ DM OL CHANGE_VECTOR$$
---------- ---------- -- -- --------------------
??????? 10 4000-01-01 I? N? FE
???????? 3 4000-01-01 U? U? 04
???????? 1 4000-01-01 D? O? 00
?
?
--然后在新庫(kù)中查看時(shí)候生效
?
SQL> select * from t1_log;
?
no rows selected
?
SQL> exec dbms_mview.refresh('wxq_t1','fast');
?
PL/SQL procedure successfully completed.
?
SQL> select * from t1_log;
?
???????? A C_ MODIFYDATE
---------- -- ----------
???????? 1 D? 2009-06-18
??????? 10 I? 2009-06-18
???????? 3 U? 2009-06-18
?
?
6、創(chuàng)建最終視圖，方便實(shí)際操作：
?
SQL> create or replace view t1 as
? 2??? select wxq_t1.a,wxq_t1.b,t1_log.c_flag,t1_log.modifydate
? 3????? from wxq_t1,t1_log
? 4???? where wxq_t1.a = t1_log.a
? 5?????? and t1_log.c_flag in ('I','U')
? 6??? union all
? 7??? select t1_log.a,wxq_t1.b,t1_log.c_flag,t1_log.modifydate
? 8????? from wxq_t1,t1_log
? 9???? where t1_log.a = wxq_t1.a(+)
10?????? and t1_log.c_flag = 'D';
?
View created.
?
SQL> select * from t1;
?
???????? A????????? B C_ MODIFYDATE
---------- ---------- -- ----------
??????? 10???????? 20 I? 2009-06-18
???????? 3???????? 40 U? 2009-06-18
???????? 1??????????? D? 2009-06-18
?
?
??? 這樣就創(chuàng)建完成了，不但為新記錄添加了時(shí)間戳，而且也增加了被刪除記錄的時(shí)間(被刪除記錄只剩下主鍵和時(shí)間字段信息)。不過(guò)這個(gè)方法有些過(guò)于繁瑣，只能在對(duì)少數(shù)表加時(shí)間戳的情況下使用，而不能全庫(kù)操作，而且針對(duì)每一個(gè)表都需要建一個(gè)trigger，效率也是個(gè)問(wèn)題。但是好處是創(chuàng)建之后對(duì)于前臺(tái)查詢時(shí)完全透明的，注意最終的查詢view名稱是與源庫(kù)的表名一致的，這樣的封裝性對(duì)前臺(tái)操作的感覺(jué)非常好。
?
?
?
?