System versioned tables in Oracle



  • A few developers approached me and asked for the easiest way to add historization to a certain table containing master data. As someone familiar with system versioned tables in the context of SQL 2011, SQL-Server ( https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15 ) and PostgreSQL ( https://blog.dbi-services.com/temporal-tables-for-postgresql-15/ ), I was decently sure that Oracle would support that standard feature.

    However, after reading and checking the whole day, I cannot find any references to actual SYSTEM VERSIONED tables. For reference, here's what the https://en.wikipedia.org/wiki/SQL:2011 about the standard and Oracles implementation:

    ...
    Definition of system-versioned tables (elsewhere called transaction time tables), using the PERIOD FOR SYSTEM_TIME annotation and WITH SYSTEM VERSIONING modifier. System time periods are maintained automatically. Constraints for system-versioned tables are not required to be temporal and are only enforced on current rows
    ...
    Oracle Oracle 12c supports temporal functionality in compliance with SQL:2011.[9] Versions 10g and 11g implement the time-sliced queries in what they call Flashback Queries, using the alternative syntax AS OF TIMESTAMP.[10] Notably both of Oracle's implementations depend on the database transaction log and so only allow temporal queries against recent changes which are still being retained for backup.

    However, even following the https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html I cannot find a single reference that shows how to get Oracle to version the rows for me. All examples reference only manual updates for the validity from and to columns.

    I think I could implement something like https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row from Data Warehousing, but that's just a development pattern with no reference how the validity is actually updated. I would really like an easy solution for the developers, as doing the updates for the validity manually is error prone (overlapping is a factor) and more work than I think is necessary.

    Is there really no system versioning in Oracle for what the SQL 2011 standard describes?



  • I use Flashback Data Archive for SCD Type 4 and Temporal Validity (plus code for automation) for SCD Type 2.

    To automate, I'd use a VIEW on the actual table with an INSTEAD OF trigger to do the actual work. Grant CRUD capability on the VIEW and Read-Only on the actual table.

    create sequence my_data_seq;
    

    create table my_data_history (
    surrogate_key int generated always as identity,
    my_data_id int default on null my_data_seq.nextval not null,
    some_data varchar2(10),
    start_date date not null,
    end_date date,
    period for current_data (start_date, end_date),
    constraint my_data_history_pk primary key (surrogate_key),
    constraint my_data_history_unique_identifier unique (my_data_id, start_date),
    constraint my_data_history_valid_date_range check ( start_date < end_date )
    );

    create view my_data
    as
    select my_data_id, some_data, start_date, end_date
    from my_data_history
    as of period for current_data( sysdate );

    create or replace trigger my_data_trg
    instead of insert or update or delete
    on my_data
    for each row
    begin
    if deleting
    then
    update my_data_history set end_date = sysdate
    where my_data_id = :old.my_data_id and start_date = :old.start_date;
    end if;

    if updating or inserting
    then
    if :old.my_data_id <> :new.my_data_id
    then
    raise_application_error( -20000, 'This method does not support modifying PK' );
    end if;

    if inserting and ( :new.my_data_id is not null or :new.start_date is not null or :new.end_date is not null)
    then
      raise_application_error( -20001, q'[No thank you! We've alrady got one (DATA_ID, START_DATE, END_DATE)]' );
    end if;
    
    if updating and :new.end_date is not null
    then
      raise_application_error( -20002, q'[Don't set END_DATE. use DELETE instead]' );
    end if;
    
    if updating and :new.start_date <> :old.start_date
    then
      raise_application_error( -20002, q'[YOU aren't allowed to modify the START_DATE]' );
    end if;
    
    
    
    
    merge into my_data_history a
    using (
      select my_data_id, some_data, start_date, end_date
      from my_data_history
      where my_data_id = :old.my_data_id and start_date = :old.start_date
      union all
      select :new.my_data_id my_data_id, :new.some_data some_data
         ,sysdate start_date, null end_date
      from dual
    ) b
    on (a.my_data_id = b.my_data_id and a.start_date = b.start_date)
    when matched then update set a.some_data=b.some_data, a.end_date = sysdate
    when not matched then insert (my_data_id, some_data, start_date)
       values ( nvl(b.my_data_id, my_data_seq.nextval), b.some_data, b.start_date);
    

    end if;
    end;

    Test Script:

    select * from my_data;
    insert into my_data some_data (some_data) values ( 'abcdefg' );
    select * from my_data;
    update my_data set some_data = 'xyz' WHERE my_data_id = 1;
    select * from my_data;
    delete from my_data where my_data_id = 1;
    select * from my_data;
    

    alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
    select * from my_data_history;


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2