Ilm in 12c - Temporal Validity

About Temporal validity

Temporal validity is an ILM feature introduced in Oracle 12c and applies to tables having date or timestamp columns. Temporal means related to time, and temporal validity means we attach a time-based validity attribute to the records in a database.

The validity of a data with respect to business rules changes over time. A record of an employee has attributes like start and end dates, which are end point markers for the period in time when the employment was in force.

The idea of temporal validity is that the application should only see data which are valid in a given temporal frame of reference (i.e. valid within a specific interval of time; within a date range interval).

Oracle allows a table designer to attach a temporal validity attributes to a record, based on values on a pair of its columns, which should be a date-time type.

Taking the example of the employee table…

Create table emp (empno number, name varchar(20), start_date date, end_date date);

To this normal table, it is possible to attach a temporal validity attribute, saying that only those records whose start/end dates envelop a specific date are valid (and visible).

Alter table emp      

add period for emp_valid_time (start_date, end_date);

By this alter table command, an invisible column by the name emp_valid_time would be added to the table to track the validity.

This sets up the emp table records, and by default, all records are valid and all records will be returned by a SELECT.  To enforce a specific temporal validity rule, it is necessary to execute a DBMS_FLASHBACK_ARCHIVE procedure called enable_at_valid_time.

This procedure specifies a date or timestamp value which must fall within the start and end date columns, in order that the record be considered temporally valid. Thus, to define a rule that only those records be considered temporally valid; for which the current system date falls between start and end date values, the procedure call would be:

  1  Begin

  2     dbms_flashback_archive.enable_at_valid_time('CURRENT');

  3* end;

SQL> /

PL/SQL procedure successfully completed.

With the rule in force, let’s examine the SQL plan for a SELECT * from emp .

The plan shows a predicate filter being applied transparently to the SELECT due to the temporal validity rule in force during that session.

SQL> select * from emp;

Execution Plan

----------------------------------------------------------

Plan hash value: 3848736563

----------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    43 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    43 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(("T"."START_DATE" IS NULL OR

              SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."START_DATE"))<=SYS_EXTRACT_UTC(SY

              STIMESTAMP(6))) AND ("T"."END_DATE" IS NULL OR

              SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."END_DATE"))>SYS_EXTRACT_UTC(SYSTI

              MESTAMP(6))))

SQL>

The rule specified by the DBMS_FLASHBACK_ARCHIVE procedure call will apply for the duration of the session only, for queries against all tables subject to temporal validity.

Below is an example with test data…

Creating the table…

SQL> Create table emp (empno number, name varchar(20), start_date date, end_date date);

Table created

Adding a valid-time dimension to the table…

SQL> Alter table emp

  2  add period for test_valid_time (start_date, end_date);

Table altered

Populating with test data… 1 record each for Jan, Feb, and March 2014.

Emp no	Start Date	End Date
1	01 jan 2014	31 jan 2014
2	01 feb 2014	28 feb 2014
3	01 mar 2013	31 mar 2014

SQL> begin

  2    insert into emp values (1, 'emp1',to_date('01jan2014','ddmmyyyy'),

  3                                   to_date('31jan2014','ddmmyyyy'));

  4    insert into emp values (2, 'emp1',to_date('01feb2014','ddmmyyyy'),

  5                                   to_date('28feb2014','ddmmyyyy'));

  6    insert into emp values (3, 'emp1',to_date('01mar2014','ddmmyyyy'),

  7                                   to_date('31mar2014','ddmmyyyy'));

  8    commit;

  9  end;

 10  /

PL/SQL procedure successfully completed

SQL>

Configuring the dbms_flashback_archive setting... for 20 Feb 2014 only .. and doing a select to test.

SQL>

SQL>   begin

  2     dbms_flashback_archive.enable_at_valid_time(

  3           level => 'ASOF',

  4          query_time => to_date('20feb2014','ddmonyyyy')

  5          );

  6    end;

  7  /

PL/SQL procedure successfully completed

SQL> SELECT * FROM EMP;

     EMPNO NAME                 START_DATE  END_DATE

---------- -------------------- ----------- -----------

         2 emp1                 01-Feb-14   28-Feb-14

Only the record with the specified valid time (20^th Feb 2014) falling in between the start/end date are returned.

About valid-time flashback queries

It is possible to over ride the DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time setting, by using what’s known as a valid-time flashback query. In this query, there is the VERSIONS clause and instead of specifying an SCN range, a valid time range is specified using this syntax.

SELECT <col-list> FROM TABLE

VERSIONS FOR <valid-time-col> between <start-value> and <end-value>

For example…

SQL> Select *

  2    from emp VERSIONS PERIOD

  3     FOR valid_time between to_date('01012014', 'ddmmyyyy') and

  4     to_date('31072014', 'ddmmyyyy');

     EMPNO NAME                 START_DATE  END_DATE

---------- -------------------- ----------- -----------

         1 emp1                 01-Jan-14   31-Jan-14

         2 emp1                 01-Feb-14   28-Feb-14

         3 emp1                 01-Mar-14   31-Mar-14

  

Configuring the dbms_flashback_archive setting... to the default ALL.. and doing a select to test.

SQL>   begin

  2     dbms_flashback_archive.enable_at_valid_time(level => 'ALL');

  3    end;

  4  /

PL/SQL procedure successfully completed

SQL> select * from emp;

     EMPNO NAME                 START_DATE  END_DATE

---------- -------------------- ----------- -----------

         1 emp1                 01-Jan-14   31-Jan-14

         2 emp1                 01-Feb-14   28-Feb-14

         3 emp1                 01-Mar-14   31-Mar-14

Now all rows are returned, as the temporal validity rule has been reset. 

About the hidden valid-time temporal column

Valid-time temporal as shown above adds a new invisible column to the employee table to track the temporal validity. The column name is emp_valid_time, as specified in the ALTER TABLE DDL.

In addition, Oracle also sets up a check constraint on the table's data. Consider this record on the emp table which has the valid-time temporal rule:

SQL> select * from emp where empno=1;

     EMPNO NAME                 START_DATE  END_DATE

---------- -------------------- ----------- -----------

         1 emp1                 01-Jan-14   31-Jan-14

When I try to update the record's start date with a date later than its end date, the update fails.

SQL> update emp set start_date=sysdate where empno=1;

update emp set start_date=sysdate where empno=1

ORA-02290: check constraint (SCOTT.VALID_TIMEB08640) violated

Checking up the constraint, we get

SQL> select constraint_name, search_condition_vc from user_constraints where table_name='EMP';

CONSTRAINT_NAME         SEARCH_CONDITION_VC

----------------------  -----------------------------

VALID_TIMEB08640        (START_DATE < END_DATE) and

                        (VALID_TIME > 0)

The valid-time temporal ALTER table statement has also resulted in this check constraint being defined automatically on the table.