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

数据仓库保留了大量的历史数据，这是区别于其他数据库的显著特点之一。历史数据能够使数据仓库重现任意时点的现场，对时间维度上的数据分析工作提供了重要的手段。

保留历史数据最常见的办法就是使用历史拉链表。历史拉链表仅当生产数据发生变更时，才相应地发生变更，既能有效保留历史数据的变动信息，又不浪费存储空间。

以数据仓库中每日增量更新的订单历史拉链表（假设表名为EVT_ORDER_HIST）为例，假设数据仓库在2010年01月02日加载一条新受理的订单记录： 订单编号 20100102 记录起效时间 2010-01-02 记录失效时间 3000-12-31 订单状态 新建 其他字段 。。。 （注：记录失效时间3000-12-31表示该记录的有效期为无限远）

2010年01月03日，该订单竣工，数据仓库将修改原来记录的失效时间，并插入一条新的记录，结果如下： 订单编号 20100102 20100102 利用以下语句，可以重现指定时点的订单数据现场，例如取订单号为20100102，在2010年1月2日的现场： SELECT *

FROM evt_order_hist

WHERE start_dt <= DATE ‘2010-01-02’ AND end_dt > DATE ‘2010-01-02’ AND

order_id = ‘20100102’;

数据仓库加载每日增量数据时，可分为两步操作：

1、 修改有变更的订单，既将失效时间为3000-12-31的记录，修改失效时间为当前时间 2、 插入新数据，起效时间为当前时间，失效时间为3000-12-31

因为不同的数据库对DELETE和UPDATE的实现代价不同，有时UPDATE会产生大量的回滚信息或日志信息，因此也有数据仓库采用DELETE代替UPDATE的做法，步骤如下： 1、 抽取原有记录，保存在临时表 2、 删除原有记录

3、 同时插入原有记录和新记录，在插入时修改记录起效和失效时间。

假设，2010年1月3日的增量订单数据存储在SRC_EVT_INC_DATA中，传统的更新办法，语句如下：

--《语句1》修改失效时间 UPDATE evt_order_hist

SET end_dt = DATE ‘2010-01-03’

WHERE end_dt = DATE ‘3000-12-31’ AND

order_id IN(SELECT order_id FROM src_evt_inc_data);

记录起效时间 2010-01-02 2010-01-03 历史失效时间 2010-01-03 3000-12-31 订单状态 新建 竣工 其他字段 。。。 。。。 --《语句2》插入新数据 INSERT INTO evt_order_hist

SELECT a.order_id, DATE ‘2010-01-03’, DATE ‘3000-12-31’, … FROM src_evt_inc_data AS a;

常见数据库表之间的关联方式有hash join和nested loop（当然，还有很多其他方式，此处暂不讨论），如果我们在《语句1》处单纯使用其中的一种方式进行更新，往往会处遇到巨大的性能问题。

当两张表通过全表扫描的方式参与关联，数据库可能会使用hash join的算法执行。在《语句1》中，如果增量数据和历史拉链表中所有的数据做hash关联，执行代价为扫描历史拉链表加上扫描增量数据。这种操作方式会随着历史拉链表数据的增加，语句执行速度逐步变慢，当数据仓库的历史数据经过一定时间的积累后，速度会慢到用户无法接受。

如果《语句1》采用nested loop方式，代价是增量数据乘上历史拉链表上索引的访问代价。由于每一次通过索引访问数据，至少会产生两次随机I/O，而随机I/O非常适合小数据量的访问场合，但是在批量数据访问时，却是非常糟糕的。所以当每日更新的数据较多时，执行速度也会降到不能接受的地步。

本人根据数据仓库实际情况，摸索出一条快速、稳定、更新时间不会跟随历史数据增加而快速增加的办法。

以每日需要更新的增量订单数据为例，按START_DT进行分组，其分布范围呈现如下形态：

我们发现需要更新的数据集中在近期新增或近期有更新的数据，距离当前时点越远的数据，被更新的几率越小。 从业务的角度理解，订单从创建、流转，到竣工往往集中在几天的时间内完成，在这期间，订单拉链数据更新地非常频繁，而更新几个月前的订单只占很少的比例。总是能在分布图上找到一个拐点A，在A点前数据量很少，但是分布范围很

广，而A点后，数据量很大而分布很集中。因此，我们根据每日更新的历史拉链数据分布特征进行优化，提出了组合更新方法，实现缩减数据仓库每日增量数据更新时间的目的。

以订单历史拉链表为例，组合更新方法具体办法如下： 1、 以START_DT，对订单历史拉链表做分区（PARTITION），可以以月为单位建立RANGE分

区表

2、 在字段“订单编号”上建立索引

3、 假设更新时间为2010-01-02，拐点A设为30天，即30天之前的订单量非常少，但分布

很广，A点之后的订单量非常多，且分布很集中，将UPDATE语句（即语句1）拆分为两句执行：

UPDATE /*+ index(a)*/evt_order_hist a SET end_dt = DATE ‘2010-01-03’

WHERE end_dt = DATE ‘3000-12-31’ AND start_dt <= DATE ‘2010-01-02’ – 30 AND

order_id IN(SELECT order_id FROM src_evt_inc_data);

UPDATE /*+ hash(a b)*/evt_order_hist a SET end_dt = DATE ‘2010-01-03’

WHERE end_dt = DATE ‘3000-12-31’ AND start_dt >= DATE ‘2010-01-02’ – 30 AND

order_id IN(SELECT order_id FROM src_evt_inc_data b);

第一句语句更新范围是早于更新时间前30天的所有历史数据，由于这一部分的数据量非常小，但涉及的时间范围非常广，因此利用历史拉链表上的索引进行更新，可以避免对大量的历史数据进行全量扫描，有效提高更新性能。

第二句语句更新范围是晚于更新时间前30天的所有历史数据，这一部分的数据量非常大，因此不适合使用索引进行更新，我们采用hash join的方式进行更新， 参与hash join的表是采用全表扫描（或全分区扫描），因为我们已经对历史拉链表建立了分区，且限定了时间范围，所以仅仅扫描更新时点附近的几个分区，扫描范围基本固定，更新时间不会随着历史数据量的积累而增加。

综上所述，历史拉链数据更新的几种办法，性能对比如下： 方法 单纯使用索引更新 代价 每日增量数据*每条记录索引访问代价 历史拉链数据+增量数据 评估 当每日增量数据较大时，性能严重下降。性能与每日增量数成反比。 随着历史数据的增加，性能会越来越差。性能与历史记录总量成反比。 随着历史数据的增加，性能基本不变；对每日增量数据变化不敏感。 单纯使用hash join更新 组合更新方法 极少量的增量数据*索引访问代价+固定范围的历史拉链数据+增量数据

组合更新方法是根据数据的具体分布情况，采用不同的策略进行更新。当然，这种方法也可推广到其他更新方式（例如用DELETE代替UPDATE）。

以上为本人在实际工作中总结的一些经验，不当之处，尽请斧正。

温州电信分公司IT部

陈驰 2010/12/21