Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
用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名称是与源库的表名一致的,这样的封装性对前台操作的感觉非常好。
 
 
 
 
posted on 2009-06-18 21:54 decode360 阅读(439) 评论(0)  编辑  收藏 所属分类: 10.DB_Tools

只有注册用户登录后才能发表评论。


网站导航: