Multi-temporal Features in Oracle 12c

Oracle 12c has a feature called Temporal Validity. With Temporal Validity, you can add one or more valid time dimensions to a table using existing columns, or using columns automatically created by the database. This means that Oracle offers combined with Flashback Data Archive native bi-temporal and even multi-temporal historization features. This blog post explains the different types of historization, when and how to use them and positions the most recent Oracle 12c database features.

Semantics and Granularity of Periods

In Flashback Data Archive Oracle defines periods with a half-open interval. This means that a point in time x is part of a period if x >= the start of the period and x < the end of the period. It is no surprise that Oracle uses also half-open intervals for Temporal Validity. The following figure visualizes the principle:

Period, Semantics and Granularity
Fig. 1: Semantics and Granularity of Periods

The advantage of a half-open interval is that the end of a preceding period is identical to the start of the subsequent period. Thus there is no gap and the granularity of a period (year, month, day, second, millisecond, nanosecond, etc.) is irrelevant. The disadvantage is that querying data at a point in time using a traditional WHERE clause is a bit more verbose compared to closed intervals since BETWEEN conditions are not applicable.

Furthermore, Oracle uses NULL for -∞ und +∞. Considering this information the WHERE clause to filter the currently valid periods looks as follows:

1) Current Periods
WHERE (vt_start IS NULL OR vt_start <= SYSTIMESTAMP)
  AND (vt_end IS NULL OR vt_end > SYSTIMESTAMP)

Use of Temporal Periods

In an entity-relationship model temporal periods may be used for transaction structure data, enterprise structure data or reference data. For transaction activity data we do not need temporal periods since the data itself contains one or more timestamps. Corrections may be done through a reversal or difference posting logic, similar to bookkeeping transactions.

The situation is similar in a dimensional model. Dimensions correspond to transaction structure data, enterprise structure and reference data and may have a temporal period (e.g. slowly changing dimensions type 2). Facts do not have temporal periods. Instead, they are modeled with one or more relationships to the time dimension. A fact is immutable. Changes are applied through new facts using a reversal or difference posting logic.

Transaction Time – TT

A flight data recorder collects and records various metrics during a flight to allow the reconstruction of the past. The transaction or system time in a data model is comparable to the functionality of such a flight data recorder. A table with a transaction time axis allows to query the current and the past state, but changes in the past or the future are not possible.

Example: Scott becomes a manager. The change of the job description from “Analyst” to “Manager” was entered into the system on April 15, 2013, at 15:42:42. The previous description Analyst is terminated at this point in time and the new description Manager becomes current at exactly the same point in time.

Oracle supports the transaction time with Flashback Data Archive (formally known as Total Recall). Using Flashback Data Archive you may query a consistent state of the past.

SCNSession ASession B
1INSERT INTO emp
   (empno, ename, job, sal, deptno)
VALUES
   (4242, 'CARTER', 'CLERK', '2400', 20);
2SELECT COUNT(*)
  FROM emp; -- 15 rows
3SELECT COUNT(*)
  FROM emp; -- 14 rows
4COMMIT;

Tab. 1: Consistent View of the Past

What is the result of the query “SELECT COUNT(*) FROM emp AS OF SCN 3” based on table 1 above? – 14 rows. This is a good and reasonable representation of the past. However, it also shows, that the consistent representation of the past is a matter of definition and in this case, it does not represent the situation of session A.

Valid Time – VT

The valid time describes the period during which something in the real world is considered valid. This period is independent of the entry into the system and therefore needs to be maintained explicitly. Changes and queries are supported in the past as well as in the future.

Example: Scott becomes a manager. The change of the job description from “Analyst” to “Manager” is valid from January 1 2014. The previous description Analyst is terminated at this point in time and the new description Manager becomes valid at exactly the same point in time. It is irrelevant when this change is entered into the System.

Decision Time – DT

The decision time describes the date and time a decision has been made. This point in time is independent of an entry into the System and is not directly related to the valid time period. Future changes are not possible.

Example: Scott becomes manager. The decision to change the job description from “Analyst” to “Manager” was made on March 24 2013. The previous job description Analyst is terminated on the decision time axis at this point in time and the new description Manager becomes current at exactly the same point in time on the decision time axis. It is irrelevant when this change is entered into the System and it is irrelevant when Scott may call himself officially a manager.

Historization Types

On one hand, the historization types are based on the time dimensions visualized in figure 2 and on the other hand categorized on the combination of these time dimensions. In this post only the most popular and generic time periods are covered. However, depending on the requirements additional, specific time periods are conceivable.

blank
Fig. 2: Historization Types

Non-temporal models do not have any time dimensions (e.g. EMP and DEPT in Schema SCOTT).

Uni-temporal models use just one time dimension (e.g. transaction time or valid time).

Bi-temporal models use exactly two time dimensions (e.g. transaction time and valid time).

Multi-temporal models use at least three time dimensions.

Tri-temporal  models are based exactly on three time dimensions.

Temporal Validity

The feature Temporal Validity covers the DDL and DML enhancements in Oracle 12c concerning temporal data management. The statements CREATE TABLE, ALTER TABLE and DROP TABLE have been extended by a new PERIOD FOR clause. Here is an example:

2) Enable Temporal Validity
SQL> ALTER TABLE dept ADD (
  2     vt_start DATE,
  3     vt_end   DATE,
  4     PERIOD FOR vt (vt_start, vt_end)
  5  );

SQL> SELECT * FROM dept;	

    DEPTNO DNAME          LOC           VT_START   VT_END
---------- -------------- ------------- ---------- ----------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

VT names the period and is a hidden column. The association of the VT period to the VT_START and VT_END columns is stored in the Oracle Data Dictionary in the table SYS_FBA_PERIOD. You need a dedicated ALTER TABLE call for every additional period.

For every period a  constraint is created to enforce positive time periods (VT_START < VT_END). However, it is not possible to define temporal constraints, e.g. prohibit overlapping periods, gaps, or orphaned parent/child periods.

Oracle 12c does not deliver support for temporal DML. Desirable would be for example:

  • insert, update delete for a given period
  • update a subset of columns for a given period
  • merge of connected and identical periods

Hence temporal changes have to be implemented as a series of conventional DML. Here is an example:

3) Temporal DML
SQL> UPDATE dept SET vt_end = DATE '2014-01-01' WHERE deptno = 30;

SQL> INSERT INTO dept (deptno, dname, loc, vt_start)
  2       VALUES (30, 'SALES', 'SAN FRANCISCO', DATE '2014-01-01');

SQL> SELECT * FROM dept WHERE deptno = 30 ORDER BY vt_start NULLS FIRST;

 DEPTNO    DNAME          LOC           VT_START   VT_END
---------- -------------- ------------- ---------- ----------
        30 SALES          CHICAGO                  2014-01-01
        30 SALES          SAN FRANCISCO 2014-01-01

Temporal Flashback Query

The feature Temporal Flashback Query covers query enhancements in Oracle 12c concerning temporal data. Oracle extended the existing Flashback Query interfaces. The FLASHBACK_QUERY_CLAUSE of the SELECT statement has been extended by a PERIOD FOR clause. Here is an example:

4) Temporal Flashback Query using SQL
SQL> SELECT *
  2    FROM dept AS OF PERIOD FOR vt DATE '2015-01-01'
  3   ORDER BY deptno;

    DEPTNO DNAME          LOC           VT_START   VT_END
---------- -------------- ------------- ---------- ----------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          SAN FRANCISCO 2014-01-01
        40 OPERATIONS     BOSTON

Instead of “AS OF PERIOD FOR” you may also use “VERSIONS PERIOD FOR”. However, it is important to notice that you may not define multiple PERIOD FOR clauses. Hence you need to filter additional temporal periods in the WHERE clause.

The PERIOD FOR clause is not applicable for views. For views, the enhancement in the PL/SQL package DBMS_FLASHBACK_ARCHIVE is interesting, especially the procedures ENABLE_AT_VALID_TIME and DISABLE_ASOF_VALID_TIME to manage a temporal context. Here is an example:

5) Temporal Flashback Query using PL/SQL Context
SQL> BEGIN
  2     dbms_flashback_archive.enable_at_valid_time(
  3        level      => 'ASOF', 
  4        query_time => DATE '2015-01-01'
  5     );
  6  END;
  7  /

SQL> SELECT * FROM dept ORDER BY deptno;

    DEPTNO DNAME          LOC           VT_START    VT_END
---------- -------------- ------------- ---------- ----------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          SAN FRANCISCO 2014-01-01
        40 OPERATIONS     BOSTON

Currently, it is not possible to define a temporal period and therefore the context is applied to every temporal period. In these cases, you have to set the context via the WHERE clause.

A limitation of Oracle 12.1.0.1 is that Temporal Flashback Query predicates are not applied in multitenant configuration. The PERIOD FOR clause in the SELECT statement and the DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME calls are simply ignored. This limitation has been lifted with Oracle 12.1.0.2.

Another limitation is, that Oracle 12 does not provide support for temporal joins and temporal aggregations.

Tri-temporal Data Model

The following data model is based on the EMP/DEPT model in the schema SCOTT. The table EMPV implements three temporal dimensions:

  • Transaction time (TT) with Flashback Data Archive
  • Valid time (VT) with Temporal Validity
  • Decision time (DT) with Temporal Validity
Tri-temporal Data Model
Fig. 3: Tri-temporal Data Model[

The table EMP is reduced to the primary key (EMPNO) which is not temporal. This allows to define and enable the foreign key constraint EMPV_EMP_MGR_FK.

The following six events will be represented with this model.

NoTransaction Time (TT)Valid Time (VT)Decision Time (DT)Action
#11Initial load from SCOTT.EMP table
#22
1990-01-01Change name from SCOTT to Scott
#331991-04-01Scott leaves the company
#441991-10-01Scott rejoins
#551989-01-01Change job from ANALYST TO Analyst
#662014-01-012013-03-24Change job to Manager and double salary

Tab. 2: Events

After the processing of all 6 events the periods for employee 7788 (Scott) in the table EMPV may be queried as follows. The transaction time is represented as the System Change Number SCN.

6) Content after Event #6
SQL> SELECT dense_rank() OVER(ORDER BY versions_startscn) event_no, empno, ename, job,
  2         sal, versions_startscn tt_start, versions_endscn tt_end,
  3         to_char(vt_start,'YYYY-MM-DD') vt_start, to_char(vt_end,'YYYY-MM-DD') vt_end,
  4         to_CHAR(dt_start,'YYYY-MM-DD') dt_start, to_char(dt_end,'YYYY-MM-DD') dt_end
  5    FROM empv VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
  6   WHERE empno = 7788 AND versions_operation IN ('I','U')
  7   ORDER BY tt_start, vt_start NULLS FIRST, dt_start NULLS FIRST;

# EMPNO ENAME JOB       SAL TT_START   TT_END VT_START   VT_END     DT_START   DT_END
-- ----- ----- ------- ----- -------- -------- ---------- ---------- ---------- ----------
 1  7788 SCOTT ANALYST  3000  2366310  2366356
 2  7788 SCOTT ANALYST  3000  2366356  2366559            1990-01-01
 2  7788 Scott ANALYST  3000  2366356  2366408 1990-01-01
 3  7788 Scott ANALYST  3000  2366408  2366559 1990-01-01 1991-04-01
 4  7788 Scott ANALYST  3000  2366424  2366559 1991-10-01
 5  7788 SCOTT ANALYST  3000  2366559                     1989-01-01
 5  7788 SCOTT Analyst  3000  2366559          1989-01-01 1990-01-01
 5  7788 Scott Analyst  3000  2366559          1990-01-01 1991-04-01
 5  7788 Scott Analyst  3000  2366559  2366670 1991-10-01
 6  7788 Scott Analyst  3000  2366670          1991-10-01                       2013-03-24
 6  7788 Scott Analyst  3000  2366670          1991-10-01 2014-01-01 2013-03-24
 6  7788 Scott Manager  6000  2366670          2014-01-01            2013-03-24

7 rows have been changed or added based on event #5 at the transaction time 2366559. It clearly shows that DML operations in a temporal model are not trivial. All the more support in that area for VT and DT is missed.

The next query filters the data for Scott on the transaction time (SYSDATE=default), valid time (2014-01-01) and decision time (2013-04-01). This way the result is reduced exactly to a single row.

7) Point-in-time Query
SQL> SELECT empno, ename, job, sal,
  2         to_char(vt_start,'YYYY-MM-DD') AS vt_start,
  3         to_char(vt_end,'YYYY-MM-DD') AS vt_end,
  4         to_CHAR(dt_start,'YYYY-MM-DD') AS dt_start,
  5         to_char(dt_end,'YYYY-MM-DD') AS dt_end
  6    FROM empv AS OF period FOR dt DATE '2013-04-01'
  7   WHERE empno = 7788 AND
  8         (vt_start <= DATE '2014-01-01' OR vt_start IS NULL) AND
  9         (vt_end > DATE '2014-01-01' OR vt_end IS NULL)
 10   ORDER BY vt_start NULLS FIRST, dt_start NULLS FIRST;

EMPNO ENAME JOB       SAL VT_START   VT_END     DT_START   DT_END
----- ----- ------- ----- ---------- ---------- ---------- ----------
 7788 Scott Manager  6000 2014-01-01            2013-03-24

Queries on multi-temporal data are relatively simple if all time periods are filtered at a point in time. The AS OF PERIOD clause (for DT) simplifies the query, but the complexity of a traditional WHERE condition (for VT) is not much higher.

Conclusion

The support for temporal data management in Oracle 12c is based on sound concepts, but the implementation is currently incomplete. I miss mainly a temporal DML API, temporal integrity constraints, temporal joins and temporal aggregations. I recommend using Oracle’s semantics for periods (half-open intervals, NULL for +/- infinity) in existing models, to simplify the migration to Temporal Validity.

In the real world, we use a lot of temporal dimensions, consciously or unconsciously at the same time. However, in data models, every additional temporal dimension increases the complexity significantly. Data models are simplifications of the real world, based on requirements and a limited budget. I do not recommend using bi-temporality or even multi-temporality as a universal design pattern. Quite the contrary I recommend determining and documenting the reason for a temporal dimension per entity to ensure that temporal dimensions are used consciously and not modeled unnecessarily.

Oracle’s Flashback Data Archive is good, transparent and since Oracle 11.2.0.4 also a cost-free option to implement requirements regarding the transaction time. For all other time dimensions such as the valid time and the decision time I recommend using standardized tooling to apply DML to temporal data.

Last update on 2015-10-24, amendments to match limitations of Oracle version 12.1.0.2.4.

8 Comments

  1. blank Raghu says:

    Excellent!! Thank you.

  2. blank rob squire says:

    Hi

    So far so good …. but this needs a lot more thought.

    All we have so far is a history of EMP called EMPV.

    What we need is similar history for DEPT because changes to DEPT should also be tracked.

    I suspect that if we used this approach to do that the queries to join EMP and DEPT would be very hard to do indeed

    Do you agree?

     

    • Hello Rob,

      In this post I focussed on the temporal features provided by the Oracle 12c database. As mentioned in the conclusion I consider the current implementation as incomplete and therefore it is indeed not that simple to join multiple temporal tables, if you cannot restrict the result on a single point in time per time dimension and table. See also Joining Temporal Intervals Part 2.

      Regards,
      Philipp

  3. blank rob squire says:

    Hi

    As you may have guessed, I have a bi-temporal implementation that has all the aspects that you miss in 12c i.e. a temporal DML API, temporal integrity constraints, temporal joins and temporal aggregations.

    It will work on any version of Oracle, even XE.

    The developer just set the time context of the query (or DML) and then uses current view joins but still has access to all versions of data in VT and TT in all tables of the schema.

    Let me know if you would like to see a demo

    Regards

    Rob

    • Hello Rob,

      Trivadis – the company I work for – has developed a generator for a bi-temporal table API as part of a multi-purpose PL/SQL library in the 90ies. Nowadays we generate the table API with Eclipse Xtext and Xtend. The table API consists of PL/SQL packages, views, instead-of triggers and configuration data. The table API creates, splits, merges and terminates periods and may be used in conjunction with common persistence frameworks such as MyBatis or Hibernate. This means the application basically uses standard DML operations. The main features of the table API are:

      • Set valid time and transaction time context (point in time or time range)
      • Allow/prohibit gaps
      • Allow/prohibit update of object identifier
      • Prohibit overlapping periods
      • Enforce temporal referential integrity constraints
      • Update a set of columns for a defined period
      • Coalesce identical adjacent periods automatically

      This approach is suited for a few DML operations per database transaction (typically OLTP). For DWH core or mart models we use a different API for bulk operations.

      Nonetheless I’d like to replace these table APIs with database features such as Flashback Data Archive or Temporal Validity. While the former is quite complete the latter is in a rather early stage.

      Your bi-temporal API sounds quite similar to the one we use. From a technical point of view I’m always interested to see how others have solved certain problems in this area, so I’ll contact you via e-mail.

      Regards,
      Philipp

  4. blank Tian Zhaoxia says:

    I want to learn more about temporal operations,including INSERT,DELETE,UPDATE,QUERY datas.I hope you give me more examples.

  5. blank amar says:

    thank you for explaining this multi temporal database features of all new oracle 12 c . We got to learn many things in detail about it here.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.