Oracle拉链表

什么是拉链表？

简单点说：记录一个事物，从开始一直到当前状态的所有变化信息。
复杂点说：记录数据在某一时间内的状态，以及数据在某一时间点的变化的数据存储方式。

为什么需要拉链表？

针对一些数据量大的表，像是保存了几年的数据，但是新增或者需要操作查看的数据只有一部分，或者只需要某个时间节点的数据，这个时候就可以使用拉链表进行操作。

怎么编写拉链表代码？

1. 创建一个拷贝了emp的临时表emp1，防止修改到emp源表。

  	   drop table emp1;--如果以前有
       alter table emp1 add up_date date; --添加了一个新增数据的时间节点字段
       insert into emp1 e1(
          e1.empno,
          e1.ename,
          e1.job,
          e1.mgr,
          e1.hiredate,
          e1.sal,
          e1.comm,
          e1.deptno,
          e1.up_date
          )
          select 
          et.empno,
          et.ename,
          et.job,
          et.mgr,
          et.hiredate,
          et.sal,
          et.comm,
          et.deptno,
          sysdate-5
       from emp et;
       select * from emp1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2. 创建目标表，用来比对emp1的数据
   重要字段start_date 、end_date 主要是这两个字段提取一段时间的数据。

---目标表
 drop table linkTable;
 create table linkTable as select * from emp where 1=2;
 select * from linkTable;
 alter table linkTable add up_date date;
 alter table linkTable add start_date date;--开始时间
 alter table linkTable add end_date date;--结束时间
 
 insert into linkTable lt(
        lt.empno,
        lt.ename,
        lt.job,
        lt.mgr,
        lt.hiredate,
        lt.sal,
        lt.comm,
        lt.deptno,
        lt.up_date,
        lt.start_date,
        lt.end_date
       
 )
        select 
          et.empno,
          et.ename,
          et.job,
          et.mgr,
          et.hiredate,
          et.sal,
          et.comm,
          et.deptno,
          et.up_date,
          to_date('2024-4-26','YYYY-MM-DD'),
          to_date('9999-9-9','YYYY-DD-MM')
        from emp1 et;
        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

3. 手动模拟新增的emp1数据

 update emp1 set sal=sal+1000,up_date=sysdate where ename='SMITH';
 insert into emp1 values(9999,'张三','xingjiapo',7369,
 				  date'1998-06-01',3600,1000,10,sysdate-1);
 select * from emp1;
 
1
2
3
4
5

4. 创建一个存储过程，封装更新数据的动作。
   开链：目标中没有改记录，新增插入该条记录并把结束时间改成无穷大。
   闭链：目标中存在该条记录，只是部分状态值不同，update该数据，并把end_date调整为当日时间。

 ---创建存储过程
        create or replace procedure updateLinkTable(startTime varchar2,endTime varchar2)
        is
        v_start date := to_date(startTime,'YYYY-MM-DD HH24:MI:SS');
        v_end date := to_date(endTime,'YYYY-MM-DD HH24:MI:SS');
        cursor update_cursor is --创建游标保存指定时间内需要比对的数据
        select * from emp1  
        where up_date>=v_start and up_date<=v_end; ---指定时间内的数据
        v_count number;
        begin 
          for i in update_cursor loop --目标表与源表比对，
            select count(1) into v_count from linkTable lk --找到当前的数据并且当前数据未过期
            where i.empno = lk.empno and lk.end_date=to_date('9999-9-9','YYYY-MM-DD');
            
            if v_count=1 then
               update linkTable lk set lk.end_date=trunc(sysdate-1) --把原先的数据更新为过期数据（历史数据） 也就是闭链
               where i.empno = lk.empno and lk.end_date=to_date('9999-9-9','YYYY-MM-DD');
               --添加当前已更新的业务变动数据,并更新end_date无穷大
               insert into linkTable lk(
                      lk.empno,
                      lk.ename,
                      lk.job,
                      lk.mgr,
                      lk.hiredate,
                      lk.sal,
                      lk.comm,
                      lk.deptno,
                      lk.up_date,
                      lk.start_date,
                      lk.end_date
               ) values(
                      i.empno,
                      i.ename,
                      i.job,
                      i.mgr,
                      i.hiredate,
                      i.sal,
                      i.comm,
                      i.deptno,
                      i.up_date,
                      trunc(sysdate),
                      to_date('9999-9-9','YYYY-MM-DD')
               );
               elsif v_count=0 then --没有匹配上说明当前数据是新增数据，直接插入进去即可 （更新end_date无穷大 开链）
                  insert into linkTable lk(
                      lk.empno,
                      lk.ename,
                      lk.job,
                      lk.mgr,
                      lk.hiredate,
                      lk.sal,
                      lk.comm,
                      lk.deptno,
                      lk.up_date,
                      lk.start_date,
                      lk.end_date
               ) values(
                      i.empno,
                      i.ename,
                      i.job,
                      i.mgr,
                      i.hiredate,
                      i.sal,
                      i.comm,
                      i.deptno,
                      i.up_date,
                      trunc(sysdate),
                      to_date('9999-9-9','YYYY-MM-DD')
               );
            end if;
          end loop;
            exception
              when others then
                dbms_output.put_line(sqlcode);
                dbms_output.put_line(sqlerrm);
        end;
        
        select * from linkTable;
        select * from emp1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

5. 调用存储过程，更新指定时间数据。

 ---方法一 pl/sql调用
begin
updateLinkTable('2024-5-28  00:00:00','2024-5-29 23:59:59');
end;
--方法二 SQL语句调用
call updateLinkTable(p_star => '2024-05-28 00:00:00',p_end => '2024-05-29 23:59:59');
-- 或者
call updateLinkTable('2024-05-28 00:00:00','2024-05-29 23:59:59');
1
2
3
4
5
6
7
8

适用场景

1. 数据量比较大
2. 表中的部分字段会被更新
3. 需要查看某一个时间点或者时间段的历史快照信息（查看某一个订单在历史某一个时间点的状态；或者某个用户在过去某段时间，下单次数）
4. 更细的比例和频率不是很大，缓慢变化（如果表中信息变化不是很大，每天保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费）

拉链表的优缺点

优点

• 节省存储空间，避免没必要的重复数据
• 能查询到历史快照

缺点

• 内存占用，查询效率受拉链长度的影响
• 断链难以恢复
• 拉链表同步逻辑复杂
• 查询性能低，存放N年的数据，表数据量大必然会影响性能
• 不好维护

拉链表常见问题

查询性能低怎么优化？
1、在一些查询引擎中，我们对生效开始时间和失效结束时间做索引，这样可以提高性能
2、保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外保留一张只提供近三个月数据的拉链表。

拉链表和流水表有什么区别？
流水表存放的是一个用户的变更记录，比如一张流水表中，一天的数据中，会存放一个用户的多条修改记录，变更几次就存几条记录，通过cancel监听mysql获取变更变化数据。但是在拉链表中只有一条记录，取当天最后一个状态。

---

以下针对数据仓库DIM层使用的拉链表做个简单理解总结

特点

1、它的适用范围是缓慢变化维，就是数据会变化但是变化频率不高
2、它存在与DIM层，也就是公共维度层
3、而ods层的更新方法是增量更新
4、它的分区是是以结束生效时间进行划分
5、它必须存在一个开始时间和一个结束时间

更新流程

术语：
生效开始时间（表示这条记录的生命周期的开始时间——周期快照时的状态）
失效开始时间（这条记录的生命周期结束时间）
end_dt=’9999-12-31’ 表示这条记录处于有效状态

流程

公共维度层的数据是以拉链表的形式进行存储的，拉链表是以生效结束时间作为分区键进行划分的。

在处理前一天的数据时，它从ods层获取到增量更新的那部分数据后，会和拉链表正在生效分区的数据，也就是9999-12-31的分区数据进行比对，如果新增的数据它是已经存在于生效分区的数据里面，那么会把正在生效分区里面的数据的生效结束生效时间置为前一天，然后新增一条一样的数据，但是生效开始时间置为前一天，生效结束时间置为9999-12-31。

如果是新增的数据，不存在与正在生效的分区里面，这条数据则会放置到生效的分区中，然后会把生效开始时间置为前一天，生效结束时间置为9999-12-31。

最后做好的分区集会以动态分区的方式，重新覆盖这个维度层拉链表。

置为前一天的那部分数据会重新生成一前一天生效结束时间的分区，然后所有生效结束时间为999-12-31的那部分数据会覆盖9999-12-31分区数据。