Oracle 12c New Feature – Temporal Validity

The Temporal Validity feature adds a time dimension to each row in the table consisting of two date-time columns to denote validity of data. Data that is older or no longer valid or not yet valid can be hidden from queries and only active data returned by queries.

In case of a very large table, just processing a small active row set rather than the entire table can be a significant performance gain.

The Temporal Validity is controlled by the user or application who defines the valid time dimension for the table via the PERIOD FOR caluse

Until now we had data and timestamp columns in table – but they record the transaction time. For example a table called EMP has a column called DOJ and this records the transaction time of when the entry was made in the EMP table for a particular employee.

But suppose we have a case where contracts are offered to potential employees on a particular date and we want to run queries based on this timestamp and not when the DOJ record for that employee is entered in the database

Let us look at an example.

We create a table MYSALES and alter the table adding the valid-time dimension TRACK_TIME via the PERIOD FOR clause as shown below
 

SQL> create table sh.mysales as select * from sh.sales;

Table created.

SQL> select count(*) from sh.mysales;

  COUNT(*)
----------
    918843

SQL> alter table sh.mysales add period for track_time;

Table altered.

Oracle will create hidden columns TRACK_TIME, TRACK_TIME_START and TRACK_TIME_END

SQL> desc mysales
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PROD_ID                                   NOT NULL NUMBER
CUST_ID                                   NOT NULL NUMBER
TIME_ID                                   NOT NULL DATE
CHANNEL_ID                                NOT NULL NUMBER
PROMO_ID                                  NOT NULL NUMBER
QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

SQL> select column_name,data_type from user_tab_cols where table_name='MYSALES' and hidden_column='YES';

COLUMN_NAME                                        DATA_TYPE
-------------------------------------------------- ----------------------------------------
TRACK_TIME                                         NUMBER
TRACK_TIME_END                                     TIMESTAMP(6) WITH TIME ZONE
TRACK_TIME_START                                   TIMESTAMP(6) WITH TIME ZONE

We can filter on valid-time columns using the SELECT statement with the PERIOD FOR clause or use the DBMS_FLASHBACK_ARCHIVE procedure.

The DBMS_FLASHBACK_ARCHIVE controls the visibility of the data as at a given time or data which is currently valid within the valid time period at the session level or can also set the visibility to the full table level.

This visibility control applies to not only SELECT statements but also to DML statements
 

SQL>  select count(*) from sh.sales;

  COUNT(*)
----------
    918843

Update the table so that only row in the table with timestamp greater than 01-JAN-2000 are considered to be valid or active.
 

SQL> update sh.mysales set track_time_start=sysdate ;

918843 rows updated.

SQL> update sh.mysales set track_time_end=sysdate where time_id < '01-JAN-2000'; 

426779 rows updated. 

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

So now when we do a count(*) from the table MYSALES, it only returns the rows which we have deemed to be active or current and not all the rows.
 

SQL> select count(*) from sh.mysales;

  COUNT(*)
----------
    492064

Let us now increase the period for the active or valid rows from 01-JAN-2001 to 01-JAN-2001
 

SQL> update sh.mysales set track_time_end=sysdate where time_id < '01-JAN-2001';

659425 rows updated.

Now when we issue the same SELECT statement, because fewer rows are considered active, the query now returns 259418 rows as opposed to the earlier 492064 rows
 

SQL>  EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

SQL> select count(*) from sh.mysales;

  COUNT(*)
----------
    259418

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL');

PL/SQL procedure successfully completed.

SQL>  select count(*) from sh.mysales;

  COUNT(*)
----------
    918843