Oracle 12c - Temporal Validity

Written by rajkiran on Monday June 9, 2014

 

Temporal validity feature allows you to add a valid time dimension to the table data by specifiying the start and end datetimes of the period for which the data should be valid and visible.
 
Flashback query clauses ASOF, VERSIONS can be used to query the data valid for a specified period of time. You can also control the data visibility at the session level by using the DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time procedure.
 

{slider Defining Temporarily Validity for the table}

In this example, Temporal validity feature is used to set the time validity for various account reward offers provided to the customers.Temporal Validity is enabled by using the clause, "add period for" for the table. OFFER_VALID column prefix is used here in this example. Three new hidden columns, viz. OFFER_VALID, OFFER_VALID_START and OFFER_VALID_END  are added to the table.

SQL> select * from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE
--------------- ----------------- ---------- ----------------
       1001            10 CURR            89898
       1002            10 CURR            89898
       2001            20 CURR            89898
       2002            20 CURR            89898
       1001            10 CURR            84372
       1002            10 CURR            84372
       2001            20 CURR            84372
       2002            20 CURR            84372

8 rows selected.

SQL>  alter table acct_offers add period for OFFER_VALID;

Table altered.

SQL>  select table_name,column_name,data_type from user_tab_cols
  2  where table_name='ACCT_OFFERS';

TABLE_NAME               COLUMN_NAME              DATA_TYPE
------------------------------ ------------------------------ ------------------------------
ACCT_OFFERS               OFFER_VALID              NUMBER
ACCT_OFFERS               OFFER_VALID_END              TIMESTAMP(6) WITH TIME ZONE
ACCT_OFFERS               OFFER_VALID_START          TIMESTAMP(6) WITH TIME ZONE
ACCT_OFFERS               ACCT_BRANCH_CODE           NUMBER
ACCT_OFFERS               ACCT_TYPE              VARCHAR2
ACCT_OFFERS               REWARDS_VENDOR_ID          NUMBER
ACCT_OFFERS               ACCT_REWARDS_ID              NUMBER

7 rows selected.


SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,OFFER_VALID_START,OFFER_VALID_END
     from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE  OFFER_VALID_STAR OFFER_VALID_END
--------------- ----------------- ---------- ----------------  --------------- ---------------
       1001            10 CURR            89898  
       1002            10 CURR            89898  
       2001            20 CURR            89898  
       2002            20 CURR            89898  
       1001            10 CURR            84372  
       1002            10 CURR            84372 
       2001            20 CURR            84372 
       2002            20 CURR            84372 

8 rows selected.


{slider Set the time validity for the data by updating the hidden columns}

Time validity is set for various offers by updating the OFFER_VALID_START and OFFER_VALID_END column values.

SQL> update acct_offers set OFFER_VALID_start=to_date('02-MAR-2014','DD-MON-YYYY'), OFFER_VALID_end=to_date('02-APR-2014','DD-MON-YYYY') 
  2  where acct_branch_code=89898 and rewards_vendor_id=10;

2 rows updated.

SQL> update acct_offers set OFFER_VALID_start=to_date('02-APR-2014','DD-MON-YYYY'), OFFER_VALID_end=to_date('02-MAY-2014','DD-MON-YYYY') 
  2  where acct_branch_code=89898 and rewards_vendor_id=20;

2 rows updated.

SQL> update acct_offers set OFFER_VALID_start=to_date('02-APR-2014','DD-MON-YYYY'), OFFER_VALID_end=to_date('02-MAY-2014','DD-MON-YYYY') 
  2  where acct_branch_code=84372 and rewards_vendor_id=20;

2 rows updated.

SQL> update acct_offers set OFFER_VALID_start=to_date('02-MAY-2014','DD-MON-YYYY'), OFFER_VALID_end=to_date('02-JUN-2014','DD-MON-YYYY') 
  2  where acct_branch_code=84372 and rewards_vendor_id=10;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE       END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
       1001            10 CURR            89898 02-MAR-2014       02-APR-2014
       1002            10 CURR            89898 02-MAR-2014       02-APR-2014
       2001            20 CURR            89898 02-APR-2014       02-MAY-2014
       2002            20 CURR            89898 02-APR-2014       02-MAY-2014
       1001            10 CURR            84372 02-MAY-2014       02-JUN-2014
       1002            10 CURR            84372 02-MAY-2014       02-JUN-2014
       2001            20 CURR            84372 02-APR-2014       02-MAY-2014
       2002            20 CURR            84372 02-APR-2014       02-MAY-2014

8 rows selected.

{slider Using Flasback queries to view the data valid for a specific period of time}

Flashback AS OF, VERSIONS queries can be used to find the data valid for a given period of time.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers as of period for OFFER_VALID to_date('10-MAR-2014');

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE       END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
       1001            10 CURR            89898 02-MAR-2014       02-APR-2014
       1002            10 CURR            89898 02-MAR-2014       02-APR-2014



SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers versions period for OFFER_VALID between sysdate-90 and sysdate;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE       END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
       1001            10 CURR            89898 02-MAR-2014       02-APR-2014
       1002            10 CURR            89898 02-MAR-2014       02-APR-2014
       2001            20 CURR            89898 02-APR-2014       02-MAY-2014
       2002            20 CURR            89898 02-APR-2014       02-MAY-2014
       2001            20 CURR            84372 02-APR-2014       02-MAY-2014
       2002            20 CURR            84372 02-APR-2014       02-MAY-2014

6 rows selected.

{slider Controlling the valid data visibility at the session level}

DBMS_FLASHBACK_ARCHIVE.ENABLE_VALID_TIME can be set at the session level to define the valid period of time for all the subsequent data operations.

Enable the session to view the offers valid only for the current time
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE       END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
       2001            20 CURR            89898 02-APR-2014       02-MAY-2014
       2002            20 CURR            89898 02-APR-2014       02-MAY-2014
       2001            20 CURR            84372 02-APR-2014       02-MAY-2014
       2002            20 CURR            84372 02-APR-2014       02-MAY-2014
Enable the session to view and update the offers valid in future, as of 1st JUN 2014.
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('01-JUN-2014','DD-MON-YYYY'));

PL/SQL procedure successfully completed.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE       END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
       1001            10 CURR            84372 02-MAY-2014       02-JUN-2014
       1002            10 CURR            84372 02-MAY-2014       02-JUN-2014


SQL> update acct_offers set rewards_vendor_id=20;      

2 rows updated.

SQL> commit;

Commit complete.

Enable the session to view all the offers defined for the past, present and future time
SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL');

PL/SQL procedure successfully completed.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2   to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3   from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE       END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
       1001            10 CURR            89898 02-MAR-2014       02-APR-2014
       1002            10 CURR            89898 02-MAR-2014       02-APR-2014
       2001            20 CURR            89898 02-APR-2014       02-MAY-2014
       2002            20 CURR            89898 02-APR-2014       02-MAY-2014
       1001            20 CURR            84372 02-MAY-2014       02-JUN-2014
       1002            20 CURR            84372 02-MAY-2014       02-JUN-2014
       2001            20 CURR            84372 02-APR-2014       02-MAY-2014
       2002            20 CURR            84372 02-APR-2014       02-MAY-2014

8 rows selected.

{slider Disabling the Temporal Validity}

Use the "drop period for" clause to disable the temporal validity for the table.

SQL> alter table acct_offers drop(period for OFFER_VALID);

Table altered.
{/sliders}

Categories: oracle database Tags: 12c database