关于数据仓库历史拉链表新更新方法的说明

1、关于数据仓库历史拉链表新更新方法的说明数据仓库保留了大量的历史数据，这是区别于其他数据库的显著特点之一。历史数据能够使数据仓库重现任意时点的现场，对时间维度上的数据分析工作提供了重要的手段。保留历史数据最常见的办法就是使用历史拉链表。历史拉链表仅当生产数据发生变更时，才相应地发生变更，既能有效保留历史数据的变动信息，又不浪费存储空间。以数据仓库中每日增量更新的订单历史拉链表（假设表名为EVT_ORDER_HIS）为例,假设数据仓库在 2010年01月02日加载一条新受理的订单记录：订单编号记录起效时间记录失效时间订单状态其他字段201001022010-01-023000-12-31新建O O

2、 。（注：记录失效时间 3000-12-31表示该记录的有效期为无限远）2010年01月03日，该订单竣工，数据仓库将修改原来记录的失效时间，并插入一条新的 记录，结果如下：订单编号记录起效时间历史失效时间订单状态其他字段201001022010-01-022010-01-03新建O O O201001022010-01-033000-12-31竣工O O O利用以下语句，可以重现指定时点的订单数据现场，例如取订单号为 20100102，在2010年1月2日的现场：SELECT *FROM evt_order_histWHERE start_dt <= DATE010-01-02 

3、9;ANDend_dt > DATE 010-01-02 'ANDorder_id = 20100102数据仓库加载每日增量数据时，可分为两步操作：1、 修改有变更的订单，既将失效时间为3000-12-31的记录，修改失效时间为当前时间2、 插入新数据，起效时间为当前时间，失效时间为3000-12-31因为不同的数据库对 DELETE和UPDATE的实现代价不同，有时UPDATE会产生大量的回 滚信息或日志信息，因此也有数据仓库采用DELETE代替UPDATE的做法，步骤如下：1、抽取原有记录，保存在临时表2、删除原有记录3、同时插入原有记录和新记录，在插入时修改记录起效和失效时

4、间。假设，2010年1月3日的增量订单数据存储在SRC_EVT_INC_DAT中，传统的更新办法，语句如下：-语句1修改失效时间UPDATE evt_order_histSET en d_dt = DATE2010-01-03'WHERE en d_dt = DATE000-12-31 ' ANDorder_id IN（SELECT order_id FROM src_evt_i nc_data）;-语句2插入新数据INSERT INTO evt_order_histSELECT a.order_id, DAT010-01-03 ：DATE000-12-31 ：FROM src

5、_evt_i nc_data AS a;常见数据库表之间的关联方式有hash join和nested loop （当然，还有很多其他方式，此处暂不讨论），如果我们在语句 1处单纯使用其中的一种方式进行更新，往往会处遇 到巨大的性能问题。当两张表通过全表扫描的方式参与关联，数据库可能会使用hash join的算法执行。在语句1中，如果增量数据和历史拉链表中所有的数据做hash关联，执行代价为扫描历史拉链表加上扫描增量数据。这种操作方式会随着历史拉链表数据的增加，语句执行速度逐步变慢，当数据仓库的历史数据经过一定时间的积累后，速度会慢到用户无法接受。如果语句1采用nested loop方式，代价是

6、增量数据乘上历史拉链表上索引的访问代价。由于每一次通过索引访问数据，至少会产生两次随机I/O,而随机I/O非常适合小数据量的访问场合，但是在批量数据访问时，却是非常糟糕的。所以当每日更新的数据较多时， 执行速度也会降到不能接受的地步。本人根据数据仓库实际情况，摸索出一条快速、稳定、更新时间不会跟随历史数据增加 而快速增加的办法。以每日需要更新的增量订单数据为例，按START_D进行分组，其分布范围呈现如下形态:我们发现需要更新的数据集中在近期新 增或近期有更新的数据，距离当前时点 越远的数据，被更新的几率越小。从业务的角度理解，订单从创建、流转， 到竣工往往集中在几天的时间内完成， 在这期间，

7、订单拉链数据更新地非常频 繁，而更新几个月前的订单只占很少的 比例。总是能在分布图上找到一个 拐点A， 在A点前数据量很少，但是分布范围很广，而A点后，数据量很大而分布很集中。因此，我们根据每日更新的历史拉链数据分布 特征进行优化，提出了 组合更新方法，实现缩减数据仓库每日增量数据更新时间的目的。以订单历史拉链表为例，组合更新方法具体办法如下：1、 以START_DT对订单历史拉链表做分区（ PARTITION，可以以月为单位建立RANGE分 区表2、在字段“订单编号”上建立索引3、 假设更新时间为2010-01-02，拐点A设为30天，即30天之前的订单量非常少，但分布 很广，A点之后的订单

8、量非常多，且分布很集中，将UPDATE语句（即语句1 ）拆分为两 句执行：UPDATE /*+ index（a）*/evt_order_hist aSET end_dt = DATE 2010-01-03'WHERE end_dt = DATE 3000-12-31 'ANDstart_dt <= DATE 2010-01-02 ' -30 ANDorder_id IN（SELECT order_id FROM src_evt_i nc_data）;UPDATE /*+ hash(a b)*/evt_order_hist aSET end_dt = DATE 20

9、10-01-03'WHERE end_dt = DATE 3000-12-31 'ANDstart_dt >= DATE 2010-01-02 ' -30 ANDorder_id IN(SELECT order_id FROM src_evt_i nc_data b);第一句语句更新范围是早于更新时间前30天的所有历史数据，由于这一部分的数据量非常小，但涉及的时间范围非常广，因此利用历史拉链表上的索引进行更新，可以避免对大量的历史数据进行全量扫描，有效提高更新性能。第二句语句更新范围是晚于更新时间前30天的所有历史数据，这一部分的数据量非常大，因此不适合使用索引进

10、行更新，我们采用hash join的方式进行更新，参与hash join的表是采用全表扫描(或全分区扫描)，因为我们已经对历史拉链表建立了分区，且限定了时间范 围，所以仅仅扫描更新时点附近的几个分区，扫描范围基本固定，更新时间不会随着历史数据量的积累而增加。综上所述，历史拉链数据更新的几种办法，性能对比如下:方法代价评估单纯使用索引更新每日增量数据*每条记录索引访问代价当每日增量数据较大时，性能 严重下降。性能与每日增量数 成反比。单纯使用hash join更新历史拉链数据+增量数据随着历史数据的增加，性能会 越来越差。性能与历史记录总 量成反比。组合更新方法极少量的增量数据 *索引访问代 价+固定范围的历史拉链数据 +