用Trigger為MView手動增加時間戳
?
?
??? 很多人都遇到過這樣的問題：源數據庫里的表沒有時間戳，或者有時間戳的字段但沒有嚴格執行，而歷史數據又可能會經常性得被修改。這對于ETL數據抽取來說將是一個很大的問題，特別是對于不能動源庫的情況下，很難解決這個問題。最近又因為這個問題，考慮了很久，最后打算在MView同步數據的基礎上，通過Trigger來手動增加時間戳。
?
??? 考慮對MView操作的原因是因為現有的數據同步就是用MView做的，據說用Stream同步數據也可以用類似的辦法添加時間戳的，這個相關的問題以后再研究，講一下MView下的操作。
?
??? 首先MView有一些天然的限制，因為最初我考慮的是在建MView的時候就添加一個SYSDATE的字段，這樣每次同步就會自動將同步時的sysdate也加入到新的物化視圖中，但是這個嘗試失敗了，因為增量(Fast)更新不支持復雜表結構，而Oracle認為新增字段的表都屬于復雜表，會報錯如下：
??? ORA-12015: cannot create a fast refresh materialized view from a complex query
??? 然后又希望能夠通過物化視圖的on commit刷新模式對表進行刷新，這樣可以直接查詢ora_rowscn來判斷某行的更新時間，但是很遺憾，on commit的刷新模式只針對同庫下的更新，不支持遠程同步，所以沒辦法只能用Trigger來自己手工增加時間戳。具體的實驗步驟如下：
?
?
?
1、在源庫中建立表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、在新
庫上添加源庫的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、在新庫創建物化視圖，設置增量刷新
?
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、在新庫創建關于表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、來測試一下在源庫中進行插入、刪除、修改操作
?
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
?
?
--然后在新庫中查看時候生效
?
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、創建最終視圖，方便實際操作：
?
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
?
?
??? 這樣就創建完成了，不但為新記錄添加了時間戳，而且也增加了被刪除記錄的時間(被刪除記錄只剩下主鍵和時間字段信息)。不過這個方法有些過于繁瑣，只能在對少數表加時間戳的情況下使用，而不能全庫操作，而且針對每一個表都需要建一個trigger，效率也是個問題。但是好處是創建之后對于前臺查詢時完全透明的，注意最終的查詢view名稱是與源庫的表名一致的，這樣的封裝性對前臺操作的感覺非常好。
?
?
?
?