数据库中的历史行管理

与许多数据库一样,我正在设计一个数据库,该数据库应记录每个表中更改的行的先前版本.

此问题的标准解决方案是为每个数据表保留历史表,并且每当需要在数据表中更新行时,当前行的副本将插入到历史表中而不是数据表中的行得到更新.

这个解决方案的缺点对我来说:

维护2个表而不是1个(如果表的结构需要更改)

应用程序需要知道两个表而不是一个

表的名称可能需要很短,以保持表名和历史表名的约定(例如,SOME_TABLE,SOME_TABLE_HIST)

我正在考虑一个不同的解决方案,并想知道它是否正常.对于每个表,我们添加列IS_LAST

当一行插入到表中时,它将插入IS_LAST = 1.

当一行更新时,原始行的副本将复制到同一个表中,并且IS_LAST = 0的更改,并且原始行将根据需要进行更新(仍保持IS_LAST = 1).

假设在我的情况下,行平均更新10次.还假设应用程序执行的操作中至少有90%仅在最新版本的行上发生.

我的数据库是一个Oracle 10g,所以为了使"活动"表保持苗条,我们可以将表拆分为2个分区:IS_LAST = 1分区和IS_LAST = 0分区.

分区是解决历史数据保持问题的好方法吗？

此解决方案是否限制了这些表的其他分区潜力？

谢谢!

第一个问题应该是:你会对这些数据做些什么？如果您没有明确的业务要求,请不要这样做.

我做了类似的事情,经过3年的运行,大约有20%的"有效数据",其余的是"以前的版本".它有1000万+ 4000万条记录.在过去三年中,我们有两(2)次调查变更历史的请求,两次请求都是愚蠢的 - 我们记录了记录变更的时间戳,我们被要求检查人员是否加班(下午5点之后).

现在,我们陷入了超大型数据库,其中包含80%无人需要的数据.

编辑:

既然你问了可能的解决方案,我会描述我们做了什么.它与您正在考虑的解决方案略有不同.

所有表都有代理主键.

所有主键都是从单个序列生成的.这很好,因为Oracle可以生成和缓存数字,因此这里没有性能问题.我们使用ORM,我们希望内存中的每个对象(以及数据库中的相应记录)都具有唯一标识符

我们使用ORM,数据库表和类之间的映射信息是以属性的形式.

我们使用以下列记录单个归档表中的所有更改:

id(代理主键)

时间戳

原始表

原始记录的ID

用户身份

事务类型(插入,更新,删除)

将数据记录为varchar2字段

这是字段名/值对形式的实际数据.

这样做是这样的:

ORM具有插入/更新和删除命令.

我们为所有业务对象创建了一个基类,它覆盖了insert/update和delete命令

insert/update/delete命令使用反射以字段名/值对的形式创建字符串.代码查找映射信息并读取字段名称,关联值和字段类型.然后我们创建类似于JSON的东西(我们添加了一些修改).当创建表示对象当前状态的字符串时,它将被插入到归档表中.

当新的或更新的对象保存到数据库表时,它将保存到目标表中,同时我们将一个带有当前值的记录插入到归档表中.

当删除对象时,我们将其从目标表中删除,同时我们在存档表中插入一条事务类型为"DELETE"的记录

优点:

我们没有数据库中每个表的归档表.架构更改时,我们也不必担心更新存档表.

完整存档与"当前数据"分开,因此存档不会对数据库造成任何性能损失.我们将它放在单独的磁盘上的单独表空间中,它工作正常.

我们创建了两个用于查看存档的表单

可以根据存档表上的过滤器列出存档表的常规查看器.过滤数据用户可以在表单上输入(时间跨度,用户,...).我们在表单fieldname/value中显示每条记录,每个更改都用颜色编码.用户可以查看每条记录的所有版本,他们可以查看更改的人员和时间.

发票查看器 - 这个很复杂,但我们创建的表单显示与原始发票输入表格非常相似的发票,但有一些额外的按钮可以显示不同的代.创建此表单需要付出相当大的努力.表格被使用了几次然后被遗忘,因为在当前的工作流程中不需要它.

用于创建存档记录的代码位于单个C#类中.数据库中的每个表都不需要触发器.

表现非常好.在高峰时段,系统被大约700-800个用户使用.这是ASP.Net应用程序.ASP.Net和Oracle都在一个带有8Gb RAM的双XEON上运行.

缺点:

单表归档格式比每个数据表都有一个归档表的解决方案更难阅读.

在归档表中搜索非id字段很难 - 我们只能LIKE在字符串上使用运算符.

因此,再次检查存档的要求.这不是一项微不足道的任务,但收益和使用可以是最小的.