用于存储差异和从增量恢复完整数据集的有效数据模型

我使用问题中描述的方式存储版本（尽管在 postgres 中）并进行了一些修改。

有两个表data_head和data_history。通过继承，它们组合成data_all，我不知道oracle中是否有类似物，可能，您可以使用类似的东西，create view as select * from data_head union all select * from data_history好吧，或者union在请求中明确使用这个。具有特定类型的请求，即 使用视图、显式联合全部或 CTE 对代表性数据进行实验。

这些表具有以下结构：

id integer, snapshot_id integer, serial_number integer, modified_date timestamp, state text

• id- 仅在 中唯一data_head，这是实体本身的标识符。
• snapshot_id它是唯一的版本标识符。
• serial_number- 版本号。随着每一次变化而增加。
• modified_date- 版本的创建日期（类似于tag）。我每天有不止一次的变化。

在某个时间点获取数据的请求如下所示：

select p.*
from data_all p
  left join data_all n on (n.id = p.id and n.serial_number = p.serial_number + 1)
where p.modified_date <= :date
   and (:date < n.modified_date OR n.modified_date IS null)
   and p.state = 'active'

当数据改变时，记录的副本data_head被插入到data_history中，并且data_head- 被更新。

我不知道如何在 Oracle 中实现所有这些，但我必须在 MySQL 中做一个类似的系统。结果出现了以下情况。

只有一张表用于存储数据——包括实际数据和历史数据。诚然，数据可变性更高，达到每月 40-50% 的水平，并且通常的每月分区足以确保足够的性能。

该方案如下：

CREATE TABLE data ( id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
                    { value_columns of different datatypes, }
                    valid_from DATE,
                    valid_till DATE DEFAULT '9999-12-31' )
PARTITION BY RANGE (valid_till) (
    PARTITION p0 VALUES LESS THAN ('2000-01-01'),
    PARTITION p1 VALUES LESS THAN ('2000-02-01'),
 -- ...
    PARTITION pN VALUES LESS THAN ('2021-05-01'),
    PARTITION pcurrent VALUES LESS THAN MAXVALUE
);

插入记录时，触发器会检查是否存在与重要字段值相同的记录 和CURRENT_DATE < valid_till。

在没有（新记录）的情况下，该记录被简单地插入。

如果可用，则更新现有记录SET valid_till = CURRENT_DATE - INTERVAL 1 DAY，插入新记录。

没错，我坚持使用两个请求而不是一个请求和一个触发器，但是同志们希望这样。

然后执行第二个查询，找到表中所有不在更新数组中且匹配的记录CURRENT_DATE < valid_till，它们也运行SET valid_till = CURRENT_DATE - INTERVAL 1 DAY。

因此，要获取某个日期的状态，我们只需获取该日期所在的所有记录BETWEEN valid_from и valid_till。分区允许您忽略 valid_till 小于给定日期的分区。

为了进一步加快这样的查询，我建议将日期转换为天数，构建并使用空间索引——他们认为这不合适。

PS。我不知道这个项目的命运。是的，它不再有趣了。

我以问题中的数据模型为基础，几乎没有任何变化。data_hist不同的是，冗余数据不保存在历史表的更改中。也就是说，第一个插入总是只进入主表。

为了在更改历史查询中考虑没有更改的新条目，它们的创建日期必须以某种方式存储。通常，该表已经有一个带有创建日期的列，例如：

create table data (id, company_name, vat_id, url, created) as [...]

给定日期的数据状态查询是对具有相关表连接（在本例中为两个表的连接）的最佳单个记录的标准搜索。它看起来像这样：

with param (asofday) as (
    select date'2021-05-20' from dual 
), t (tag, id, company_name, vat_id, url, delete_flag) as (
    select date'9999-12-31', id, company_name, vat_id, url, null
    from data, param
    where created <= param.asofday 
    union all
    select tag, id, company_name, vat_id, url, delete_flag
    from data_hist)
select lastchanged, id, company_name, vat_id, url, delete_flag
from t t1
cross apply (
    select max (t2.tag) keep (dense_rank last order by tag desc) lastchanged
    from t t2, param
    where t2.id = t1.id
    and t2.tag >= param.asofday
    group by t2.id
) where tag = lastchanged
order by id;

LASTCHANGED                 ID COMPA VAT_I URL     DELETE_FLAG
------------------- ---------- ----- ----- ------- -----------
2021-05-20 00:00:00          1 aaa-3 123-3 aaa.com            
2021-05-20 00:00:00          2 bbb   111   bbb.de             
2021-05-21 00:00:00          3 ccc   345-2 ccc.de             
2021-05-25 00:00:00          4 xxx   789   xxx.com           1

插入“临时阶段”准备的新数据的简化示例：

declare
    st stagetab;
begin
    select stagerow (
        s.id, s.company_name, s.vat_id, s.url, s.created, 
        case when d.id is null then 0 else 1 end)  
    bulk collect into st
    from stage s
    left join data d on d.id = s.id;
    dbms_output.put_line ('staged '||sql%rowcount); 

    insert into data_hist
    select trunc (sysdate), id, company_name, vat_id, url, null  
    from data d
    where exists (
        select 1 
        from table (st) s
        where s.id = d.id and s.flagexists = 1);
    dbms_output.put_line ('copied '||sql%rowcount); 
    
    delete 
    from data d
    where exists (
        select 1 
        from table (st) s
        where s.id = d.id and s.flagexists = 1);
    dbms_output.put_line ('deleted '||sql%rowcount); 

    insert into data 
        select id, company_name, vat_id, url, created
        from table (st);
    dbms_output.put_line ('inserted '||sql%rowcount); 
end;
/

用样本数据和创建添加。db<>fiddle上的对象。

闪回解决方案。此解决方案不需要创建额外的表，它负责保存和删除更改历史记录。所有更改都将作为 UNDO 记录（或更改向量 oldval=>newval）存储在单独的表空间中，也就是说，以用于事务回滚和一致读取的相同格式存储。

您将需要一个可供应用程序用户访问的附加表空间：

create tablespace data_archive
    datafile '/path/dbfiles/oradata/cdb1/pdb1/data_archive01.dbf' 
    size 4M segment space management auto
/
alter user me quota unlimited on data_archive
/

接下来，创建一个存档并指示其中的数据存储时间（例如，6 个月），授予用户对新创建的存档的访问权限：

create flashback archive data_hist
tablespace data_archive quota 4M retention 6 month
/
grant flashback archive on data_hist to me;
/

表格可以选择将更改的数据保存在创建的存档中：

create table me.data (id primary key, company_name, vat_id, url) as
    select rownum, 'company'||rownum, 'vat-'||rownum, 'domen'||rownum||'.com' 
    from dual connect by level<=9
/
alter table me.data flashback archive data_hist
/

从现在开始，表中所有更改的历史data可以使用 6 个月，并且可以通过带有AS OF子句的简单查询获得：

update me.data set company_name=company_name||'*', vat_id=vat_id||'*'
/
9 rows updated.

commit;

Commit complete.

select * 
from me.data
union all
select * 
from me.data as of timestamp (timestamp'2021-06-07 01:10:00')
/

        ID COMPANY_NAME     VAT_ID   URL             
---------- ---------------- -------- ----------------
         1 company1*        vat-1*   domen1.com      
         2 company2*        vat-2*   domen2.com      
[...]
         9 company9*        vat-9*   domen9.com      
         1 company1         vat-1    domen1.com      
         2 company2         vat-2    domen2.com       
[...]