Oracle Temporal Validity

Hi everyone,

In this article I will mention the temporal validity subject which is a new feature that has come with Oracle 12c R1. I hope that will be a beneficial article in terms of awareness.

Temporal validity offers us an infrastructure where we can render the data we have, valid for a particular time range and query our table depended on the time. It’s actually a mechanism that allows us to examine the status of the data in the table or to use it, in any time interval.

Now let’s get to the examples quickly and try to understand the subject.

Firstly let’s talk about the scenario. Let there be an invoice table and a validity interval assigned by us for the every entry in this table. Then let’s change our session parameters according to the validity dates of the inserted entries and query our table in a time period we want.

 

CREATE TABLE hr.invoice
  (
    custid    NUMBER,
    inv_no    NUMBER,
    inv_total NUMBER,
    inv_date  DATE,
    t_date    TIMESTAMP
  ); 

INSERT INTO hr.invoice VALUES  (1,1,100,sysdate-30,sysdate);
INSERT INTO hr.invoice VALUES  (1,2,900,sysdate-60,sysdate);
INSERT INTO hr.invoice VALUES  (1,3,700,sysdate-90,sysdate);
INSERT INTO hr.invoice VALUES  (1,4,600,sysdate-120,sysdate);
INSERT INTO hr.invoice VALUES  (1,5,300,sysdate-150,sysdate);
INSERT INTO hr.invoice VALUES  (1,6,200,sysdate-180,sysdate);
COMMIT;

We’ve created our table and inserted data in it. Now, it’s time to run the feature that will grant our table a time dimension.

We provided a time dimension to our table by using the PERIOD FOR phrase. We could also do this while creating our table like below.

CREATE TABLE hr.invoice
  (
    custid    NUMBER,
    inv_no    NUMBER,
    inv_total NUMBER,
    inv_date  DATE,
    t_date    TIMESTAMP,
    PERIOD FOR valid_time
  ); 

(You can see the article which includes different uses of syntax of the PERIOD FOR phrase from the links at the end of this article)
We have created the infrastructure where we can query our table according to time with the previous process. Actually Oracle has already created 2 invisible columns in the background using the valid_time name that we had given with the PERIOD FOR phrase. The names of these are;
valid_time_start
valid_time_end
If we query them in the dictionary we will not be able to see their existence as they are invisible.

SELECT owner,
  table_name,
  column_name,
  data_type
FROM all_tab_columns
WHERE owner    = 'HR'
AND table_name = 'INVOICE';

tmv1

Now we have provided them with time dimension with the PERIOD FOR phrase, let us update valid_time_start and valid_time_end columns which are created as invisible and whose validity according to time we can adjust, for every entry.

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '20' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE inv_no         =1;


UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '20' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE inv_no         =2;

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '15' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '5'  MINUTE
WHERE inv_no         =3;

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '19' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE inv_no         =4;

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '14' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '3'  MINUTE
WHERE inv_no         =5;

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP + INTERVAL '2' MINUTE,
    valid_time_end   = SYSTIMESTAMP + INTERVAL '5'  MINUTE
WHERE inv_no         =6;

COMMIT;

With the updates we have made, we made every one of the entries valid in a particular time. Now, what kind of a result appears when I query my table without touching my session’s flashback setting?

tmv2

I was able to view all the data in my table when I queried without touching the flashback setting. Good, now how can I query the table for a particular period or a point of date?

This question has many answers; now let’s look at the methods in order.

QUERYING THE DATA ACCORDING TO THE TIME

1- DBMS_FLASHBACK_ ARCHIVE : You can do the query using DBMS_FLASHBACK_ARCHIVE package. However, in order to use it, the user we connect with should have an authorization on that package.

GRANT ALL ON SYS.dbms_flashback_archive TO HR;

After authorizing the user, now we can adjust the time as session based and re-run our query.

EXECUTE SYS.DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ASOF', SYSTIMESTAMP - INTERVAL '7' MINUTE);

With the line above, we enabled “the 7 minutes ago” in our session. Now let us look at the valid data in that time interval.

SELECT * from hr.invoice; 

tmv3

We listed the entries that were valid 7 minutes ago. From this example, do not think that it’s only possible to go back in time. We can go to a time period where the validity is forward in time and examine the case in that time.

EXECUTE SYS.DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ASOF', SYSTIMESTAMP + INTERVAL '3' MINUTE);

We have enabled the “3 minutes after”. Now let’s query again.

SELECT * from hr.invoice; 

tmv4

As can be seen, the entry that would be valid after 3 minutes has appeared.

2- AS OF PERIOD FOR and VERSIONS PERIOD FOR phrases: Like the previous example, let us query the 7 minutes ago with this method.

SELECT * from hr.invoice AS OF PERIOD FOR valid_time SYSTIMESTAMP - INTERVAL '7' MINUTE;

tmv5

And now, let’s query the time period between 10 minutes and 18 minutes ago.

SELECT * from hr.invoice VERSIONS PERIOD FOR valid_time between  SYSTIMESTAMP - INTERVAL '18' MINUTE and  SYSTIMESTAMP - INTERVAL '10' MINUTE;

tmv6

With the methods above, we can query our data however we want. We can even pull the data with the 2nd method and join it with other tables. In addition to that, in Temporal Validity infrastructure, in columns that include unique key constraint in our table, it’s given permission to violation of this constraint while it’s guaranteed that the entries will never overlap in means of time. It means that entries containing the same unique key can be present in the table more than one on condition of all being in different validity periods.

REFERENCES

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s