DBA Hub

📋Steps in this guide1/6

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

Make querying of effective date ranges simpler using temporal validity in Oracle database 12c.

oracle 12cconfigurationintermediate
by OracleDba
16 views
1

Setup

The following script creates three tables to store student and module records, along with the relationship between them. The following query displays the raw data. A question we may want to ask is, which students were on active modules on a specific date? We could do this as follows.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
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;

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>

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

Define Valid Periods

Temporal validity allows you to use the clause to define valid time periods on a table using start and end or columns. These valid time periods can be used in queries against the table. In the previous example, the table could have a valid time period defined on the and columns. This is done during table creation in the following way. Periods can be defined against existing tables using the command. Some of the later examples assume the following period has been defined.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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)
);

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

alter table student_modules add period for student_module_period (start_date, end_date);
3

AS OF PERIOD FOR Queries

With a period defined on the table, we display students that were on active modules on a specific date using the following query. In the same way, students who are currently on active modules are displayed by using .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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>

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

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 syntax. Just looking at the last week we get the following.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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>

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

Temporal Validity and Flashback

Temporal validity can easily be combined with flashback technology. Here is a repeat a previous query. Check the current SCN. Modify the data to make another student active. Flashback to view the data before the update using the package. Alternatively, specify the SCN as well as the period in the query to get the same result as the previous example.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
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>

select dbms_flashback.get_system_change_number from dual;

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

1 row selected.

SQL>

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>

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;

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

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. Perform a 10053 trace of the statement. The section beginning with "Final query after transformations" shows the statement that was actually processed, after the query transformation. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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>

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

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"

Comments (0)

Please to add comments

No comments yet. Be the first to comment!