{"id":1009,"date":"2014-01-04T13:19:18","date_gmt":"2014-01-04T12:19:18","guid":{"rendered":"http:\/\/www.salvis.com\/blog\/?p=1009"},"modified":"2023-11-07T22:08:03","modified_gmt":"2023-11-07T21:08:03","slug":"multi-temporal-database-features-in-oracle-12c","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2014\/01\/04\/multi-temporal-database-features-in-oracle-12c\/","title":{"rendered":"Multi-temporal Features in Oracle 12c"},"content":{"rendered":"\n

Oracle 12c has a feature called Temporal Validity<\/em>. 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.<\/p>\n\n\n\n

Semantics and Granularity of Periods<\/h2>\n\n\n\n

In Flashback Data Archive Oracle defines periods with a half-open interval. This means that a point in time x<\/strong> is part of a period if x<\/strong> >= the start of the period and x<\/strong> < 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:<\/p>\n\n\n\n

\"Period,<\/a>
Fig. 1: Semantics and Granularity of Periods<\/figcaption><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

Furthermore, Oracle uses NULL for -\u221e und +\u221e. Considering this information the WHERE clause to filter the currently valid periods looks as follows:<\/p>\n\n\n\n

1) Current Periods<\/span><\/path><\/path><\/svg><\/span>
WHERE<\/span> (vt_start <\/span>IS<\/span> <\/span>NULL<\/span> <\/span>OR<\/span> vt_start <= SYSTIMESTAMP)<\/span><\/span>\n  <\/span>AND<\/span> (vt_end <\/span>IS<\/span> <\/span>NULL<\/span> <\/span>OR<\/span> vt_end > SYSTIMESTAMP)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Use of Temporal Periods<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Transaction Time – TT<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
\n\n\n\n\t\n\n\t\n\t\n\t\n\t
SCN<\/th>Session A<\/th>Session B<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>INSERT INTO emp
\n   (empno, ename, job, sal, deptno)
\nVALUES
\n   (4242, 'CARTER', 'CLERK', '2400', 20);<\/td>		<\/td>\n<\/tr>\n
2<\/td>SELECT COUNT(*)
\n  FROM emp; -- 15<\/font> rows<\/td>		<\/td>\n<\/tr>\n
3<\/td><\/td>SELECT COUNT(*) 
\n  FROM emp; -- 14<\/font> rows<\/td>\n<\/tr>\n
4<\/td>COMMIT;<\/td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Tab. 1: Consistent View of the Past<\/h2>\n<\/span><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Valid Time – VT<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Decision Time – DT<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Historization Types<\/h2>\n\n\n\n
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.<\/p>\n\n\n
\n
\"\"<\/a>
Fig. 2: Historization Types<\/figcaption><\/figure>\n<\/div>\n\n\n
<\/p>\n\n\n\n
Non-temporal models do not have any time dimensions (e.g. EMP and DEPT in Schema SCOTT).<\/p>\n\n\n\n
Uni-temporal models use just one time dimension (e.g. transaction time or valid time).<\/p>\n\n\n\n
Bi-temporal models use exactly two time dimensions (e.g. transaction time and valid time).<\/p>\n\n\n\n
Multi-temporal models use at least three time dimensions.<\/p>\n\n\n\n
Tri-temporal  models are based exactly on three time dimensions.<\/p>\n\n\n\n
Temporal Validity<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
2) Enable Temporal Validity<\/span><\/path><\/path><\/svg><\/span>
SQL> ALTER TABLE dept ADD (<\/span><\/span>\n  2     vt_start DATE,<\/span><\/span>\n  3     vt_end   DATE,<\/span><\/span>\n  4     PERIOD FOR vt (vt_start, vt_end)<\/span><\/span>\n  5  );<\/span><\/span>\n<\/span><\/span>\nSQL> SELECT * FROM dept;\t<\/span><\/span>\n<\/span><\/span>\n    DEPTNO DNAME          LOC           VT_START   VT_END<\/span><\/span>\n---------- -------------- ------------- ---------- ----------<\/span><\/span>\n        10 ACCOUNTING     NEW YORK<\/span><\/span>\n        20 RESEARCH       DALLAS<\/span><\/span>\n        30 SALES          CHICAGO<\/span><\/span>\n        40 OPERATIONS     BOSTON<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Oracle 12c does not deliver support for temporal DML. Desirable would be for example:<\/p>\n\n\n\n