How to Implement Temporal Validity in an Oracle Database

Overview of Temporal Validity

Temporal Validity is also referred to as Valid Time Support.  The objective is to allow tables to have one or more valid time dimensions.  That is, the data is visible (“valid”) depending on its time-based validity.  The valid time is determined by start and end dates or timestamps of a valid time period.

temporal validity

Examples of where this might be used include:

  • Hiring and finishing dates of an employee
  • Valid period for an insurance policy
  • Date of change of address for a customer or client

Think of your medical insurance policy which typically changes from year to year.  If a claim is submitted in January 2017 for a clinic visit that occurred in December 2016, it should be processed based on the policy coverage for 2016, not 2017.

Temporal Validity is typically activated with  AS OF and VERSIONS BETWEEN or  DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time.  It can be set to show all table data (the default) or valid data as of a specified time.

Valid time dimensions are enforced by additional columns that are automatically added to a table.  Two date-time columns result from a CREATE TABLE or ALTER TABLE command.  They can be added explicitly or created automatically from the PERIOD specification.  One NUMBER column is also created with the same name as specified PERIOD.  The columns are hidden columns, therefore, DESCRIBE and SELECT * will not show them.  They must be explicitly selected.

CREATE TABLE emp_vt (
empno NUMBER(6) NOT NULL
,ename VARCHAR2(20),
PERIOD FOR emp_vt_time);

SELECT column_name
,data_type
,column_id AS col_id
,segment_column_id AS seg_col_id
,internal_column_id AS int_col_id
,hidden_column
,virtual_column
FROM user_tab_cols WHERE table_name = ‘EMP_VT’;dec4-1

The third column is a numeric value key to tie each set of begin and end times together.

Using Valid Times With SQL

INSERT INTO emp_vt(empno,ename,emp_vt_time_start,emp_vt_time_end)
VALUES (1023,’COX’ ,
’01-APR-2016 12.00.01 PM GMT’ ,
’05-APR-2016 12.00.01 PM GMT’);                       – – Valid from April 1 to 5

INSERT INTO emp_vt(empno,ename,emp_vt_time_start,emp_vt_time_end)
VALUES (1024,’ADAMS’,
’06-APR-2016 12.00.01 PM GMT’,                        – – Valid for one year from
’05-APR-2017 12.00.01 PM GMT’);                       – – April 6, 2016 to April 5, 2017

SELECT * FROM emp_vt;

EMPNO   ENAME
———-  ————
1023        COX                                                               – – Note that the hidden columns
1024        ADAMS                                                         – – are not being shown

SELECT * FROM emp_vt AS OF PERIOD FOR
emp_vt_time ’03-APR-2016 12.00.00 PM’;

EMPNO ENAME
———– —————–                                                              – – Only COX is a valid
1023         COX                                                               – – employee on April 3

SELECT * FROM emp_vt
WHERE emp_vt_time_start > ’05-APR-2016 12.00.00 PM’;

EMPNO ENAME
———- ———————                                                             – – Only ADAMS is a valid
1024       ADAMS                                                             – – employee after April 5

To see the hidden columns, you must explicitly select them:

SELECT empno,ename,emp_vt_time_start,emp_vt_time_end,emp_vt_time
FROM emp_vt;

dec4-3

There is no support for primary keys on the valid time columns.  If an employee has numerous valid periods, each additional period would require three extra columns for each valid time period.

Session-Level Control of Visibility 

Session-level control uses the DBMS_FLASHBACK_ARCHIVE package.  To have visibility of data valid at a specific point in time, the time will be found using the package.

EXEC dbms_flashback_archive.enable_at_valid_time –
(‘ASOF’, ’01-APR-16 12.00.01 PM’)

To see visibility only of currently valid data:

EXEC dbms_flashback_archive.enable_at_valid_time (‘CURRENT’)

To see full data visibility (default):

EXEC dbms_flashback_archive.enable_at_valid_time (‘ALL’)

The visible time periods are enforced internally using check constraints.

Table With User-Generated Start and End Columns

Many have already been doing this manually using columns previously defined in the columns, constraints, and triggers.  Temporal Validity can take advantage of the columns and then automate the process for you.

In the past:

CREATE TABLE emp_mine
(empno NUMBER,
ename VARCHAR2(12),
stime DATE,
etime DATE);

SELECT * FROM  EMP_MINE;

dec4-4

To convert the user-defined columns to take advantage of Temporal Validity:

ALTER TABLE emp_mine ADD(PERIOD FOR pd(stime,etime));

This step makes Oracle aware of you user-defined columns.Now use the columns:

Temporal Validity

dec4-5

Note that the user-generated columns can be of datatype DATE and are not hidden.  The table can now be used with  AS OF and VERSIONS BETWEEN or  DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time as shown above.

image sources

  • dec4-1: Kent Hinckley
  • dec4-3: Kent Hinckley
  • dec4-4: Kent Hinckley
  • dec4-5: Kent Hinckley

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.