8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Temporal Validity in Oracle Database 12c Release 1 (12.1)

The temporal validity feature has been added in Oracle database 12c to make querying of effective date ranges simpler.

Related articles.

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.