8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Setup
- Define Valid Periods
- AS OF PERIOD FOR Queries
- VERSIONS PERIOD FOR ... BETWEEN Queries
- Temporal Validity and Flashback
- Query Transformation
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:
- Temporal Validity Support
- Using Oracle Flashback Technology
- DBMS_FLASHBACK
- Temporal Validity in Oracle Database 12c Onward
- Overlapping Date Ranges
Hope this helps. Regards Tim...