Thursday, 6 October 2016

Temporal Validity in Oracle Database 12c

The temporal validity feature has been added in Oracle database 12c to make querying of effective date ranges simpler.
  • Setup
The following script creates three tables to store student and module records, along with the relationship between them.

CONN sys@pdb1 AS SYSDBA

GRANT EXECUTE ON dbms_flashback TO test;

CONN test/test@pdb1
DROP TABLE student_modules PURGE;
DROP TABLE students PURGE;
DROP TABLE modules PURGE;

CREATE TABLE modules (
  id         NUMBER(10) NOT NULL,
  name       VARCHAR2(100) NOT NULL,
  CONSTRAINT modules_pk PRIMARY KEY (id)
);

INSERT INTO modules VALUES (1, 'Physiology');
INSERT INTO modules VALUES (2, 'Ecology');
INSERT INTO modules VALUES (3, 'Evolution');
COMMIT;


CREATE TABLE students (
  id         NUMBER(10) NOT NULL,
  first_name VARCHAR2(100) NOT NULL,
  last_name  VARCHAR2(100) NOT NULL,
  CONSTRAINT students_pk PRIMARY KEY (id)
);

INSERT INTO students VALUES (1, 'Charles', 'Xavier');
INSERT INTO students VALUES (2, 'Erik', 'Lehnsherr');
INSERT INTO students VALUES (3, 'Jean', 'Gray');
INSERT INTO students VALUES (4, 'Aurora', 'Munroe');
COMMIT;


CREATE TABLE student_modules (
  id         NUMBER(10) NOT NULL,
  student_id NUMBER(10) NOT NULL,
  module_id  NUMBER(10) NOT NULL,
  start_date DATE,
  end_date   DATE,
  CONSTRAINT student_modules_pk PRIMARY KEY (id),
  CONSTRAINT stmo_stud_fk FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT stmo_modu_fk FOREIGN KEY (module_id) REFERENCES modules(id)
);

CREATE INDEX stmo_stud_fk_i ON student_modules(student_id);
CREATE INDEX stmo_modu_fk_i ON student_modules(module_id);


INSERT INTO student_modules VALUES 
  (1, 1, 1, TO_DATE('01-JAN-2012','DD-MON-YYYY'), TO_DATE('10-FEB-2012','DD-MON-YYYY'));
INSERT INTO student_modules VALUES 
  (2, 1, 2, TO_DATE('01-FEB-2012','DD-MON-YYYY'), TO_DATE('15-MAR-2012','DD-MON-YYYY'));
INSERT INTO student_modules VALUES 
  (3, 1, 3, TO_DATE('01-JAN-2012','DD-MON-YYYY'), TO_DATE('01-APR-2012','DD-MON-YYYY'));

INSERT INTO student_modules VALUES 
  (4, 2, 1, TO_DATE('01-JAN-2012','DD-MON-YYYY'), TO_DATE('10-FEB-2012','DD-MON-YYYY'));
INSERT INTO student_modules VALUES 
  (5, 2, 2, TO_DATE('01-FEB-2012','DD-MON-YYYY'), TO_DATE('15-MAR-2012','DD-MON-YYYY'));
INSERT INTO student_modules VALUES 
  (6, 2, 3, TO_DATE('01-JAN-2012','DD-MON-YYYY'), TO_DATE('01-APR-2012','DD-MON-YYYY'));

INSERT INTO student_modules VALUES 
  (7, 3, 1, TO_DATE('01-JAN-2013','DD-MON-YYYY'), TO_DATE('10-FEB-2013','DD-MON-YYYY'));
INSERT INTO student_modules VALUES 
  (8, 3, 2, TO_DATE('01-FEB-2013','DD-MON-YYYY'), TO_DATE('15-MAR-2013','DD-MON-YYYY'));
INSERT INTO student_modules VALUES 
  (9, 3, 3, TO_DATE('01-JAN-2013','DD-MON-YYYY'), TO_DATE('01-APR-2013','DD-MON-YYYY'));

INSERT INTO student_modules VALUES 
  (10, 4, 1, TO_DATE('01-JAN-2014','DD-MON-YYYY'), TO_DATE('10-FEB-2014','DD-MON-YYYY'));
INSERT INTO student_modules VALUES 
  (11, 4, 2, TO_DATE('01-FEB-2014','DD-MON-YYYY'), TO_DATE('15-MAR-2014','DD-MON-YYYY'));
INSERT INTO student_modules VALUES 
  (12, 4, 3, TO_DATE('01-JAN-2014','DD-MON-YYYY'), NULL);

COMMIT;

The following query displays the raw data.

ALTER SESSION SET nls_date_format='DD-MON-YYYY';

COLUMN first_name FORMAT A10
COLUMN last_name FORMAT A10
COLUMN module_name FORMAT A10
COLUMN start_date FORMAT A11
COLUMN end_date FORMAT A11

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
---------------- --------------   --------------- ---------------  ----------------
01-JAN-2012 10-FEB-2012      Charles       Xavier             Physiology
01-JAN-2012 10-FEB-2012      Erik             Lehnsherr      Physiology
01-JAN-2012 01-APR-2012      Charles       Xavier             Evolution
01-JAN-2012 01-APR-2012      Erik             Lehnsherr      Evolution
01-FEB-2012 15-MAR-2012     Charles       Xavier            Ecology
01-FEB-2012 15-MAR-2012     Erik             Lehnsherr      Ecology
01-JAN-2013 10-FEB-2013      Jean            Gray              Physiology
01-JAN-2013 01-APR-2013      Jean            Gray              Evolution
01-FEB-2013 15-MAR-2013     Jean            Gray              Ecology
01-JAN-2014 10-FEB-2014      Aurora         Munroe         Physiology
01-JAN-2014             Aurora     Munroe       Evolution
01-FEB-2014 15-MAR-2014     Aurora        Munroe          Ecology

12 rows selected.

SQL>

A question we may want to ask is, which students were on active modules on a specific date. We could do this as follows.

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
WHERE  (sm.start_date IS NULL
        OR
        sm.start_date <= TO_DATE('12-FEB-2013','DD-MON-YYYY'))
AND    (sm.end_date IS NULL
        OR
        sm.end_date >= TO_DATE('12-FEB-2013','DD-MON-YYYY'))
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
-------------     -----------      ----------    ----------    ----------
01-JAN-2013  01-APR-2013     Jean        Gray       Evolution
01-FEB-2013  15-MAR-2013    Jean        Gray       Ecology

2 rows selected.

SQL>
  • Define Valid Periods
Temporal validity allows you to use the PERIOD FOR clause to define valid time periods on a table using start and end DATE or TIMESTAMP columns. These valid time periods can be used in queries against the table. In the previous example, the STUDENT_MODULES table could have a valid time period defined on the START_DATE and END_DATE columns. This is done during table creation in the following way.

CREATE TABLE student_modules (
  id         NUMBER(10) NOT NULL,
  student_id NUMBER(10) NOT NULL,
  module_id  NUMBER(10) NOT NULL,
  start_date DATE,
  end_date   DATE,
  CONSTRAINT student_modules_pk PRIMARY KEY (id),
  CONSTRAINT stmo_stud_fk FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT stmo_modu_fk FOREIGN KEY (module_id) REFERENCES modules(id),
  PERIOD FOR student_module_period (start_date, end_date)
);

Periods can be defined against existing tables using the ALTER TABLE command.

-- Create a period using existing columns.
ALTER TABLE student_modules ADD PERIOD FOR student_module_period (start_date, end_date);

-- Remove a period.
ALTER TABLE student_modules DROP (PERIOD FOR student_module_period);

-- Create a period with system generated hidden columns.
ALTER TABLE student_modules ADD PERIOD FOR student_module_period;
Some of the later examples assume the following period has been defined.

ALTER TABLE student_modules ADD PERIOD FOR student_module_period (start_date, end_date);
  • AS OF PERIOD FOR Queries
With a period defined on the STUDENT_MODULES table, we display students that were on active modules on a specific date using the following query.

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules AS OF 
         PERIOD FOR student_module_period TO_DATE('12-FEB-2013','DD-MON-YYYY') sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
------------     -----------   ----------   ----------   ----------
01-JAN-2013 01-APR-2013    Jean        Gray       Evolution
01-FEB-2013 15-MAR-2013   Jean        Gray       Ecology

2 rows selected.

SQL>

In the same way, students who are currently on active modules are displayed by using SYSDATE.

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules AS OF 
         PERIOD FOR student_module_period SYSDATE sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE   FIRST_NAME LAST_NAME  MODULE_NAM
-------------   -----------   ----------   ----------   ----------
01-JAN-2014                     Aurora     Munroe     Evolution

1 row selected.

SQL>
  • VERSIONS PERIOD FOR ... BETWEEN Queries
We can easily take this a step further and find students that were on active modules during a specified time period. This is done using the VERSIONS PERIOD FOR ... BETWEEN syntax.

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules VERSIONS PERIOD FOR student_module_period BETWEEN
         TO_DATE('12-FEB-2013','DD-MON-YYYY') AND TO_DATE('06-JAN-2014','DD-MON-YYYY') sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
-------------   -----------   ----------   ----------   ----------
01-JAN-2013 01-APR-2013    Jean       Gray       Evolution
01-FEB-2013 15-MAR-2013   Jean        Gray       Ecology
01-JAN-2014 10-FEB-2014    Aurora    Munroe   Physiology
01-JAN-2014                          Aurora    Munroe   Evolution

4 rows selected.

SQL>

Just looking at the last week we get the following.

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules VERSIONS PERIOD FOR student_module_period BETWEEN
         TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE) sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
----------- ----------- ---------- ---------- ----------
01-JAN-2014             Aurora     Munroe     Evolution

1 row selected.

SQL>
  • Temporal Validity and Flashback
Temporal validity can easily be combined with flashback technology. Here is a repeat a previous query.

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules AS OF 
         PERIOD FOR student_module_period TO_DATE('12-FEB-2013','DD-MON-YYYY') sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
-------------   -----------   ----------   ----------   ----------
01-JAN-2013 01-APR-2013      Jean       Gray       Evolution
01-FEB-2013 15-MAR-2013     Jean        Gray       Ecology

2 rows selected.

SQL>

Check the current SCN.

SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 5816693

1 row selected.

SQL>

Modify the data to make another student active.

UPDATE student_modules
SET    end_date = NULL
WHERE  id = 1;

COMMIT;

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules AS OF 
         PERIOD FOR student_module_period TO_DATE('12-FEB-2013','DD-MON-YYYY') sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
-------------   -----------   ----------   ----------   ----------
01-JAN-2012                             Charles    Xavier     Physiology
01-JAN-2013 01-APR-2013       Jean         Gray        Evolution
01-FEB-2013 15-MAR-2013      Jean         Gray        Ecology

3 rows selected.

SQL>

Flashback to view the data before the update using the DBMS_FLASHBACK package.

EXEC DBMS_FLASHBACK.enable_at_system_change_number(5816693);

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules AS OF 
         PERIOD FOR student_module_period TO_DATE('12-FEB-2013','DD-MON-YYYY') sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
-------------   -----------   ----------   ----------   ----------
01-JAN-2013 01-APR-2013    Jean       Gray       Evolution
01-FEB-2013 15-MAR-2013   Jean        Gray       Ecology

2 rows selected.

SQL>

EXEC DBMS_FLASHBACK.disable;

Alternatively, specify the SCN as well as the period in the query to get the same result as the previous example.

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules
         AS OF SCN 5816693
         AS OF PERIOD FOR student_module_period TO_DATE('12-FEB-2013','DD-MON-YYYY') sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

START_DATE  END_DATE    FIRST_NAME LAST_NAME  MODULE_NAM
-------------   -----------   ----------   ----------   ----------
01-JAN-2013 01-APR-2013    Jean       Gray       Evolution
01-FEB-2013 15-MAR-2013   Jean        Gray       Ecology

2 rows selected.

SQL>
  • Query Transformation
It's worth keeping in mind this new functionality is a query transformation. If we take one of the previous queries and perform a 10053 trace we can see this.

Check the trace file for the session.

SELECT value FROM v$diag_info WHERE  name = 'Default Trace File';

VALUE
----------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_14874.trc

1 row selected.

SQL>

Perform a 10053 trace of the statement.

ALTER SESSION SET EVENTS '10053 trace name context forever';

SELECT sm.start_date,
       sm.end_date,
       s.first_name,
       s.last_name,
       m.name AS module_name      
FROM   student_modules AS OF 
         PERIOD FOR student_module_period TO_DATE('12-FEB-2013','DD-MON-YYYY') sm 
       JOIN students s ON sm.student_id = s.id
       JOIN modules m ON sm.module_id = m.id
ORDER BY 1, 2, 3;

ALTER SESSION SET EVENTS '10053 trace name context off';

The section beginning with "Final query after transformations" shows the statement that was actually processed, after the query transformation.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."START_DATE" "START_DATE",
       "T"."END_DATE" "END_DATE",
       "S"."FIRST_NAME" "FIRST_NAME",
       "S"."LAST_NAME" "LAST_NAME",
       "M"."NAME" "MODULE_NAME"
FROM   "TEST"."STUDENT_MODULES" "T",
       "TEST"."STUDENTS" "S",
       "TEST"."MODULES" "M"
WHERE  "T"."MODULE_ID"="M"."ID"
AND    "T"."STUDENT_ID"="S"."ID"
AND    ("T"."START_DATE" IS NULL
        OR 
        "T"."START_DATE"<=TO_DATE(' 2013-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       )
AND    ("T"."END_DATE" IS NULL
        OR
        "T"."END_DATE">TO_DATE(' 2013-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       )
ORDER BY "T"."START_DATE","T"."END_DATE","S"."FIRST_NAME"

As you can see, the statement has been rewritten to a form we might have used prior to 12c.