Эффективная модель данных для хранения различий и восстановления полного датасета из дельты

Я использую описаный в вопросе способ для хранения версий (правда в postgres) с несколькими модификациями.

Есть две таблицы data_head и data_history. Через наследование они объеденены в data_all, не знаю есть ли аналог в oracle, вероятно, можно обойтись чем-то вроде create view as select * from data_head union all select * from data_history ну или явно этот union в запросе использовать. С конкретным видом запроса, т.е. использовать view, явный union all или 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 меньше заданной даты.

Для дополнительного ускорения такого запроса я предлагал конвертировать дату в номер дня, построить и использовать spatial индекс - сочли нецелесообразным.

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.

Решение с flashback. Для этого решения не надо создавать дополнительных таблиц, заботится о сохранении и удалении истории изменений. Все изменения будут сохраняться в отдельном табличном прострастве в виде 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       
[...]