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.
Examples of where this might be used include:
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’;
The third column is a numeric value key to tie each set of begin and end times together.
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 5INSERT 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;
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 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.
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;
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
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
