Temporal Validity in Oracle Database 12C release 1 (12.1)

Temporal Validity is very interesting feature in Oracle 12C that provides ability to scan effectively Gantt data:

  • adds (one or more) “time dimension” to a table by using current columns or using columns automatically created by database
  • enable using simple SQL syntax to filter the columns to access only active data using Oracle flashback technology

Following picture shows five records Rec1 … Rec5 that are active only in a period of time (represented in the picture as |————|). Usually the most complex part for programmers is to filter active records in specific dedicated time:

  • for Filter1 – valid records are: Rec1, Rec2, Rec3, Rec5
  • for Filter2 – valid records are: Rec1, Rec2, Rec4, Rec5
  • for Filter3 – valid records are: Rec1, Rec4, Rec5

temporal_validity_1

Typical example could be Employee table with two “time dimension” columns :

  • hire_start_date – when employee started to work
  • hire_end_data – when employee ended work

Test data

Let’s start with some test data. Table TEST_TBL is created with some dummy data. The table hasn’t got yet time dimension.

CREATE TABLE test_tbl
(
  emp_id    NUMBER,
  emp_name  VARCHAR2(10),
  salary    NUMBER
);

INSERT INTO test_tbl
SELECT level, 'emp_'||level, level*5
FROM dual
CONNECT BY LEVEL <= 5;

COMMIT;

SELECT * 
FROM test_tbl;

    EMP_ID EMP_NAME       SALARY
---------- ---------- ----------
         1 emp_1               5 
         2 emp_2              10 
         3 emp_3              15 
         4 emp_4              20 
         5 emp_5              25

ADD PERIOD FOR

Following statement adds “time dimension”.

ALTER TABLE test_tbl ADD PERIOD FOR valid_time;

“Time dimension” is represented by new columns (all invisible) that will store “time dimension” data:

  • valid_time          – NUMBER
  • valid_time_start – TIMESTAMP(6) WITH TIME ZONE
  • valid_time_end  – TIMESTAMP(6) WITH TIME ZONE

another option is to add own columns and use them as “time dimension”.

ALTER TABLE test_tbl
ADD
(
  own_start_date DATE,
  own_end_date   DATE
);

ALTER TABLE test_tbl 
  ADD PERIOD FOR own_period (own_start_date, own_end_date);

In this case only one invisible column is added:

  • own_period – NUMBER

Here is list of visible columns

SELECT column_name, column_id
  FROM user_tab_columns
 WHERE table_name='TEST_TBL'
ORDER BY column_id;

COLUMN_NAME     COLUMN_ID
-------------- ----------
EMP_ID                1
EMP_NAME              2
SALARY                3
OWN_START_DATE        4
OWN_END_DATE          5

Here is list of all columns

SELECT column_name, column_id
  FROM user_tab_cols
 WHERE table_name='TEST_TBL'
ORDER BY column_id;

COLUMN_NAME       DATA_TYPE                     COLUMN_ID
----------------- ---------------------------- ----------
EMP_ID            NUMBER                                1 
EMP_NAME          VARCHAR2                              2 
SALARY            NUMBER                                3
OWN_START_DATE    DATE                                  4
OWN_END_DATE      DATE                                  5              
OWN_PERIOD        NUMBER                                  
VALID_TIME        NUMBER                                  
VALID_TIME_END    TIMESTAMP(6) WITH TIME ZONE            
VALID_TIME_START  TIMESTAMP(6) WITH TIME ZONE

It’s time to update period columns to fill data for the first “time dimension” valid_time. Please notice that you can explicit use invisible columns.

UPDATE test_tbl
SET 
  valid_time_start = to_date('01-06-2000','dd-mm-yyyy'), 
  valid_time_end = to_date('15-08-2004','dd-mm-yyyy')
WHERE emp_id IN (1);

UPDATE test_tbl
SET 
  valid_time_start = to_date('01-06-1995','dd-mm-yyyy'), 
  valid_time_end = to_date('15-08-2004','dd-mm-yyyy')
WHERE emp_id IN (2);

UPDATE test_tbl
SET 
  valid_time_start = to_date('01-09-1999','dd-mm-yyyy'), 
  valid_time_end = to_date('01-03-2009','dd-mm-yyyy')
WHERE emp_id IN (3);

UPDATE test_tbl
SET 
  valid_time_start = to_date('01-09-2010','dd-mm-yyyy'), 
  valid_time_end = to_date('01-03-2012','dd-mm-yyyy')
WHERE emp_id IN (4);

UPDATE test_tbl
SET 
  valid_time_start = to_date('20-05-1998','dd-mm-yyyy')
WHERE emp_id IN (5);

COMMIT;

So now we have following data in table TEST_TBL

SELECT
  emp_id,
  emp_name,
  salary,
  valid_time,
  to_char(valid_time_start,'DD.MM.YYYY') valid_time_start,
  to_char(valid_time_end,'DD.MM.YYYY')   valid_time_end
FROM test_tbl
ORDER BY emp_id;

EMP_ID EMP_NAME SALARY VALID_TIME VALID_TIME_START VALID_TIME_END
------ -------- ------ ---------- ---------------- --------------
     1 emp_1         5    9330585 01.06.2000       15.08.2004    
     2 emp_2        10    9330585 01.06.1995       15.08.2004    
     3 emp_3        15    9330585 01.09.1999       01.03.2009    
     4 emp_4        20    9330585 01.09.2010       01.03.2012    
     5 emp_5        25    9330585 20.05.1998

Following GANTT report shows when the records are valid

WITH periods AS
( 
  SELECT 
    emp_name label,
    CAST(valid_time_start   AS DATE) start_date,
    nvl(CAST(valid_time_end AS DATE), SYSDATE) end_date
    FROM test_tbl
  ORDER BY valid_time_start
), 
limits AS
( 
  SELECT
    MIN(start_date) per_start,
    MAX(end_date)   per_end,
    40              width
   FROM periods
),
bars AS
( 
  SELECT   
    label,
    lpad(label, '6')||'|' activity,
    (start_date - per_start)/(per_end - per_start) * width from_pos,
    (end_date - per_start)/(per_end - per_start) * width   to_pos
  FROM     
    periods, limits
  ORDER BY label  
)
SELECT  
  label, 
  activity||lpad('I',from_pos)||
  rpad('-', to_pos - from_pos, '-')||'I' gantt
FROM     bars
UNION ALL
SELECT 'TOTAL', lpad('|',7)
       ||to_char(per_start,'DD-MON-YYYY')
       ||lpad(to_char(per_end,'DD-MON-YYYY'), width - 11)
FROM   limits
/

LABEL      GANTT                                             
---------- --------------------------------------------------
emp_1       emp_1|         I--------I                        
emp_2       emp_2|------------------I                        
emp_3       emp_3|       I-------------------I               
emp_4       emp_4|                              I---I        
emp_5       emp_5|     I---------------------------------I   
TOTAL            |01-JUN-1995                  28-OCT-2014   

 6 rows selected

AS OF PERIOD FOR

Following syntax

SELECT .. FROM ... AS OF PERIOD <period name> TO_DATE()

lets to filter easily records for specific day that are active

SELECT 
  emp_id,
  emp_name,
  to_char(valid_time_start,'dd.mm.yyyy') "Start",
  to_char(valid_time_end,'dd.mm.yyyy') "End"
FROM test_tbl 
  AS OF PERIOD FOR valid_time to_date('01.09.1998', 'DD.MM.YYYY')
ORDER BY 2;
    EMP_ID EMP_NAME   Start      End      
---------- ---------- ---------- ----------
         2 emp_2      01.06.1995 15.08.2004 
         5 emp_5      20.05.199

VERSIONS PERIOD FOR … BETWEEN

show range of valid records

SELECT 
  emp_id,
  emp_name,
  to_char(valid_time_start,'dd.mm.yyyy') "Start",
  to_char(valid_time_end,'dd.mm.yyyy') "End"
FROM test_tbl
  VERSIONS PERIOD FOR valid_time 
   BETWEEN to_date('01.01.2009', 'DD.MM.YYYY') AND
           to_date('01.09.2012', 'DD.MM.YYYY')
ORDER BY 2;

    EMP_ID EMP_NAME   Start      End      
---------- ---------- ---------- ----------
         3 emp_3      01.09.1999 01.03.2009 
         4 emp_4      01.09.2010 01.03.2012 
         5 emp_5      20.05.1998

DBMS_FLASHBACK_ARCHIVE

Package DBMS_FLASHBACK_ARCHIVE enables to see active records without specifying PERIOD FOR.

BEGIN
  dbms_flashback_archive.enable_at_valid_time('CURRENT');
END;
/

SELECT 
  emp_id,
  emp_name,
  to_char(valid_time_start,'dd.mm.yyyy') "Start",
  to_char(valid_time_end,'dd.mm.yyyy') "End"
FROM test_tbl 
ORDER BY 2;
    EMP_ID EMP_NAME   Start      End      
---------- ---------- ---------- ----------
         5 emp_5      20.05.1998

or for specific day

BEGIN
  dbms_flashback_archive.enable_at_valid_time
  ('ASOF',to_date('01.09.1999', 'DD.MM.YYYY'));
END;
/

SELECT 
  emp_id,
  emp_name,
  to_char(valid_time_start,'dd.mm.yyyy') "Start",
  to_char(valid_time_end,'dd.mm.yyyy') "End"
FROM test_tbl 
ORDER BY 2;

    EMP_ID EMP_NAME   Start      End      
---------- ---------- ---------- ----------
         2 emp_2      01.06.1995 15.08.2004 
         3 emp_3      01.09.1999 01.03.2009 
         5 emp_5      20.05.1998

reset settings to see all records and ignoring “PERIOD” columns

BEGIN
  dbms_flashback_archive.enable_at_valid_time('ALL');
END;
/

SELECT 
  emp_id,
  emp_name,
  to_char(valid_time_start,'dd.mm.yyyy') "Start",
  to_char(valid_time_end,'dd.mm.yyyy') "End"
FROM test_tbl 
ORDER BY 2;

    EMP_ID EMP_NAME   Start      End      
---------- ---------- ---------- ----------
         1 emp_1      01.06.2000 15.08.2004 
         2 emp_2      01.06.1995 15.08.2004 
         3 emp_3      01.09.1999 01.03.2009 
         4 emp_4      01.09.2010 01.03.2012 
         5 emp_5      20.05.1998

Temporal Validity and Flashback Technology

Temporal Validity syntax can be combined with flashback technology

alter system checkpoint;

SELECT dbms_flashback.get_system_change_number FROM dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3094264

SELECT
  emp_id,
  emp_name,
  salary,
  to_char(valid_time_start,'DD.MM.YYYY') valid_time_start,
  to_char(valid_time_end,'DD.MM.YYYY') valid_time_end
FROM test_tbl 
  AS OF PERIOD FOR valid_time to_date('01.09.2014', 'DD.MM.YYYY')
ORDER BY emp_id;

    EMP_ID EMP_NAME       SALARY VALID_TIME_START VALID_TIME_END
---------- ---------- ---------- ---------------- --------------
         5 emp_5              25 20.05.1998

modify records

UPDATE test_tbl
SET 
  valid_time_end = null
WHERE emp_id IN (4);

COMMIT;

SELECT
  emp_id,
  emp_name,
  salary,
  to_char(valid_time_start,'DD.MM.YYYY') valid_time_start,
  to_char(valid_time_end,'DD.MM.YYYY') valid_time_end
FROM test_tbl 
  AS OF PERIOD FOR valid_time to_date('01.09.2014', 'DD.MM.YYYY')
ORDER BY emp_id;

    EMP_ID EMP_NAME       SALARY VALID_TIME_START VALID_TIME_END
---------- ---------- ---------- ---------------- --------------
         4 emp_4              20 01.09.2010                      
         5 emp_5              25 20.05.1998

and still see previous versions according to flashback and temporal validity

SELECT
  emp_id,
  emp_name,
  salary,
  to_char(valid_time_start,'DD.MM.YYYY') valid_time_start,
  to_char(valid_time_end,'DD.MM.YYYY') valid_time_end
FROM test_tbl 
  AS OF SCN 3094264
  AS OF PERIOD FOR valid_time to_date('01.09.2014', 'DD.MM.YYYY')
ORDER BY emp_id;

    EMP_ID EMP_NAME       SALARY VALID_TIME_START VALID_TIME_END
---------- ---------- ---------- ---------------- --------------
         5 emp_5              25 20.05.1998

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *