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
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