Oracle Database 12c - Temporal Validity

> Database > Oracle Database

1 - About

Temporal validity permits to retrieve the data as it was in a point in time by defining a validity period for which a given record is considered valid.

This time-based validity is determined by the start and end dates of a period (defined by two columns named also dimension). (This columns can be hidden to the user).

By declaring a valid point in time in a session, it gives time validity access transparently to all tables that holds temporal support. The user don't need explicitly to add a where clause statement such as for instance

WHERE start_date >= mydate AND end_date <= mydate

3 - Example

The best example in a datawarehouse environment is a Slowly Changing Dimensions type 2 where we trace all changes of a record.

For instance, when the employee Nico changes this name to “Gerard Nico” the 30 Juny 2011 and that we want to trace this change we end up with the following data structure.

EMPNO LAST_NAME    START_TIME END_TIME
----- ------------ ---------- ----------
  100 Nico         01-JAN-10  30-JUN-11
  100 Gerard Nico  30-JUN-11
 

Where the start time and the end time column define what Oracle calls the temporal validity of the data.

Advertising

4 - Privileges

The database user must have:

  • the SELECT privilege on the objects in the select statement
  • not necessarily others elevated privileges in order to use the Temporal validity functionality (create table, select)
  • the privilege to use the dbms_flashback_archive PL/SQL package in order to set the point in time validity of a session.
GRANT ALL ON "SYS"."dbms_flashback_archive" TO USER;

5 - Period Syntax definition

The validity period column is defined in a create table statement or in an alter table statement.

It is defined with the following columns:

  • a period column. It's an INVISIBLE virtual column with the period name of data type NUMBER.
  • and two columns that define the start and end data time of the period. This columns can be:
    • of a data time data type (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE)
    • modified and inserted

Alter Example:

ALTER TABLE my_emp ADD PERIOD FOR period_name (start_time_column, end_time_column);

where:

  • the (start_time_column, end_time) statement is optional. If they are not defined in the statement , they are automatically created by Oracle Database as INVISIBLE (you cannot make them VISIBLE) with the following name:
    • PERIOD_NAME_START
    • and PERIOD_NAME_END
Advertising

6 - Row validity

If you will find NULL in The Row is valid
start_time_column before the value of end_time_column
end_time_column after the value of start_time_column.
neither column between the values of start_time_column and end_time_column, inclusive.
both columns all time values

7 - Limitations

7.1 - Temporal validity

Limitations

  • Temporal validity is not supported with a multitenant container database (CDB). The database must not be a Pdb. Oracle will return no errors but will also not return the expected result.
  • ILM is not supported with OLTP table compression for Temporal Validity. Segment-level ILM and compression is supported if partitioned on the end-time columns.
  • ILM is not supported with OLTP table compression for in-database archiving. Segment-level ILM and compression is supported if partitioned on the ORA_ARCHIVE_STATE column.

7.2 - Flashback Queries

8 - Steps

The following step by step was extracted and extended from the Oracle® Database Development Guide: Design Basic: 1.9.4 Temporal Validity

8.1 - Management of the validity period

The PERIOD FOR statement enable Temporal Validity for a table.

CREATE TABLE my_emp(
  empno NUMBER,
  last_name VARCHAR2(30),
  start_time TIMESTAMP,
  end_time TIMESTAMP,
PERIOD FOR user_valid_time (start_time, end_time));
ALTER TABLE my_emp ADD PERIOD FOR user_valid_time (start_time, end_time);
ALTER TABLE my_emp DROP (PERIOD FOR user_valid_time);
Advertising

8.2 - Temporal Validity Columns

If the periode clause doesn't set two columns explicitly two hidden columns are added to the table:

  • PERIOD_NAME_START
  • and PERIOD_NAME_END

This columns are not visible in the output of:

Name                    Null?    Type
----------------------- -------- ----------------
EMPNO                            NUMBER
LAST_NAME                        VARCHAR2(30)
START_TIME                       TIMESTAMP(6)
END_TIME                         TIMESTAMP(6)

You can see them with the *_TAB_COLS tables (USER_TAB_COLS,DBA_TAB_COLS, ALL_TAB_COLS).

SELECT
  SUBSTR( COLUMN_NAME, 1, 22 ) NAME,
  SUBSTR( DATA_TYPE, 1, 28 ) DATA_TYPE,
  HIDDEN_COLUMN
FROM
  USER_TAB_COLS
WHERE
  TABLE_NAME = 'MY_EMP';
NAME            DATA_TYPE       HID
--------------- --------------- ---
END_TIME        TIMESTAMP(6)    NO
START_TIME      TIMESTAMP(6)    NO
LAST_NAME       VARCHAR2        NO
EMPNO           NUMBER          NO
USER_VALID_TIME NUMBER          YES

8.3 - Insertion of sample data

SELECT * FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TIMESTAMP_FORMAT';
DD-MM-YYYY HH.MI.SSXFF AM
-- Employee 100 with history 
INSERT INTO my_emp VALUES (100, 'Nick', NULL, '01-01-2010');
INSERT INTO my_emp VALUES (100, 'Nico', '01-01-2010', '30-06-2011');
INSERT INTO my_emp VALUES (100, 'Gerard Nico', '30-06-2011', NULL);
 
-- Employee 101 with history
INSERT INTO my_emp VALUES (101, 'Daniel', '01-01-2011', '30-07-2011');
INSERT INTO my_emp VALUES (101, 'Dany', '30-07-2011', NULL);
 
-- Employee 102 without 
INSERT INTO my_emp VALUES (102, 'Jos', '01-01-2013', NULL);
 
-- Employee 103 with Nulls
INSERT INTO my_emp VALUES (103, 'Ghost', NULL, NULL);

8.4 - Retrieve Rows (with visibility definition of temporal data)

The visibility of temporal data can be define:

8.4.1 - Session

Set session-level visibility of temporal data usingdbms_flashback_archive.enable_at_valid_time with the following option:

You can also implement session-level Flashback using the DBMS_FLASHBACK package.

8.4.1.1 - Current

Current records is the currently valid data (SYSDATE)

BEGIN
 dbms_flashback_archive.enable_at_valid_time('CURRENT');
END;
/
SELECT * FROM  my_emp;
EMPNO LAST_NAME    START_TIME END_TIME
----- ------------ ---------- ----------
  100 Gerard Nico  30-JUN-11
  101 Dany         30-JUL-11
  102 Jos          01-JAN-13
  103 Ghost
8.4.1.2 - All

All records is the default.

BEGIN
 sys.dbms_flashback_archive.enable_at_valid_time('ALL');
END;
/
SELECT * FROM  my_emp;
EMPNO LAST_NAME    START_TIME END_TIME
----- ------------ ---------- ----------
  100 Nick                    01-JAN-10
  100 Nico         01-JAN-10  30-JUN-11
  100 Gerard Nico  30-JUN-11
  101 Daniel       01-JAN-11  30-JUL-11
  101 Dany         30-JUL-11
  102 Jos          01-JAN-13
  103 Ghost
8.4.1.3 - As of
BEGIN
 sys.dbms_flashback_archive.enable_at_valid_time('ASOF',TO_DATE( '30-06-2011', 'DD-MM-YYYY' ));
END;
/
SELECT * FROM  my_emp;
EMPNO LAST_NAME    START_TIME END_TIME
----- ------------ ---------- ----------
  100 Gerard Nico  30-JUN-11
  101 Daniel       01-JAN-11  30-JUL-11
  103 Ghost

8.4.2 - Query

8.4.2.1 - Standard

With a standard query, the default row validity period is ALL. It means that you will see all data.

SELECT
  *
FROM
  my_emp;
EMPNO LAST_NAME    START_TIME END_TIME
----- ------------ ---------- ----------
  100 Nick                    01-JAN-10
  100 Nico         01-JAN-10  30-JUN-11
  100 Gerard Nico  30-JUN-11
  101 Daniel       01-JAN-11  30-JUL-11
  101 Dany         30-JUL-11
  102 Jos          01-JAN-13
  103 Ghost
8.4.2.2 - Flashback Query

Oracle Flashback Query is used to retrieve rows from table based on whether they are considered valid as of a specified time or during a specified time period. See flashback_query_clause - Oracle® Database SQL Language Reference 12c Release 1 (12.1)

Flashback Query retrieve data from a table, view, or materialized view based on time dimensions associated with the data.

  • As of. Select of only one employee in a point in time.
-- What's the valid information as of today?
SELECT * FROM my_emp AS OF PERIOD FOR user_valid_time SYSDATE;
EMPNO LAST_NAME    START_TIME END_TIME
----- ------------ ---------- ----------
  100 Gerard Nico  30-JUN-11
  101 Dany         30-JUL-11
  102 Jos          01-JAN-13
  103 Ghost
-- What's the valid information as of 30-06-2011?
SELECT * FROM my_emp 
AS OF PERIOD 
FOR user_valid_time TO_DATE( '30-06-2011', 'DD-MM-YYYY' )
EMPNO LAST_NAME    START_TIME END_TIME
----- ------------ ---------- ----------
  100 Gerard Nico  30-JUN-11
  101 Daniel       01-JAN-11  30-JUL-11
  103 Ghost
  • Versions. Select of multiple version of the employee rows.
SELECT
  *
FROM
  my_emp VERSIONS PERIOD 
  FOR user_valid_time BETWEEN TO_DATE( '01-01-2010','DD-MM-YYYY' ) AND SYSDATE
WHERE
  empno = 100;
EMPNO LAST_NAME    START_TIME END_TIME
----- ------------ ---------- ----------
  100 Nico         01-JAN-10  30-JUN-11
  100 Gerard Nico  30-JUN-11

9 - Documentation / Reference

db/oracle/temporal_validity.txt · Last modified: 2017/09/06 19:29 by gerardnico