Date Tracking with Temporal Validity in Oracle 12c

It’s always nice to see new database features, and I especially like to see features which implement concepts that are commonly coded at the application level. I’ve posted a number of blogs recently on the new Analytic Views functionality in 12.2, however today I’m going to look at a feature introduced a little earlier in 12c (12.1) – Temporal Validity.

It’s probably best explained with an example, so let’s take a look at some sample (simplified) date-tracked data such as employees in a Human Resources system.

For anyone not familiar with what the above is representing, we see that up until 17-Aug-2016 we have an employee named Alice Jones working in the Finance department. From 18-Aug-2016 she has changed her name to Alice Smith, still working in the Finance department. Then from 01-Nov-2016 she begins working in Human Resources up until 18-Nov-2016 when she left the company. This representation of data allows us to query historic data and see what the status of an employee was at any given point in time. First let’s create a sample data model to represent that.

Create Table xxemployees (   person_id             Number Not Null,   full_name             Varchar2(100) Not Null,   department            Varchar2(100) Not Null,   status                Varchar2(20) Not Null,   effective_start_date  Date Not Null,   effective_end_date    Date Not Null );  Insert Into xxemployees(person_id,full_name,department,status,effective_start_date,effective_end_date) Select 1, 'Alice Jones','Finance','Active Employee',To_Date('01-Jan-2016','DD-Mon-YYYY'),To_Date('17-Aug-2016','DD-Mon-YYYY') From Dual Union All Select 1, 'Alice Smith','Finance','Active Employee',To_Date('18-Aug-2016','DD-Mon-YYYY'),To_Date('31-Oct-2016','DD-Mon-YYYY') From Dual Union All Select 1, 'Alice Smith','Human Resources','Active Employee',To_Date('01-Nov-2016','DD-Mon-YYYY'),To_Date('18-Nov-2016','DD-Mon-YYYY') From Dual Union All Select 1, 'Alice Jones','Human Resources','Inactive Employee',To_Date('19-Nov-2016','DD-Mon-YYYY'),To_Date('31-Dec-4712','DD-Mon-YYYY') From Dual; 

Ignore the non-normalisation – this is for a demo only. 🙂 Note – This is the first blog where I’ve used Oracle Live SQL for a blog – it’s an excellent resource and definitely worth a look.
Anyway, we want to query what department our example employee was in on the 4th July 2016. Traditionally we would do the following (and this is how systems such as Oracle E-Business Suite work):

Select full_name, department   From xxemployees  Where person_id = 1    And To_Date('01-Jul-2016','DD-Mon-YYYY') Between effective_start_date And effective_end_date; 

Perfect… apart from the fact that the database is unaware of the structure of our data, in that it is date-tracked and accessed by an effective date. Similarly, that dummy end date of 31-Dec-4712 might make it easier from an application coding perspective as we don’t need to deal with Nulls, however it can cause problems with high values on at the database column statistics level. Well, the temporal validity functionality in 12.1 resolves this for us. What we do at table creation time is tell the database that our table is holding time-series data. You can alter tables instead of dropping and recreating them, however the default functionality is to implement the time series as >= and < (or is null) rather than a between which would be >= and <=, so we need to tweak the dates in our example. Also, we have the option of not specifying the date columns and instead allowing Oracle to create them for us (as hidden columns). So let’s do that.

Create Table xxemployees (   person_id             Number Not Null,   full_name             Varchar2(100) Not Null,   department            Varchar2(100) Not Null,   status                Varchar2(20) Not Null ); 
-- Enable Temporal Validity on the table Alter Table xxemployees Add Period For effective_date;  -- Re-Seed our data Insert Into xxemployees(person_id,full_name,department,status,effective_date_start,effective_date_end) Select 1, 'Alice Jones','Finance','Active Employee',To_Date('01-Jan-2016','DD-Mon-YYYY'),To_Date('18-Aug-2016','DD-Mon-YYYY') From Dual Union All Select 1, 'Alice Smith','Finance','Active Employee',To_Date('18-Aug-2016','DD-Mon-YYYY'),To_Date('01-Nov-2016','DD-Mon-YYYY') From Dual Union All Select 1, 'Alice Smith','Human Resources','Active Employee',To_Date('01-Nov-2016','DD-Mon-YYYY'),To_Date('19-Nov-2016','DD-Mon-YYYY') From Dual Union All Select 1, 'Alice Jones','Human Resources','Inactive Employee',To_Date('19-Nov-2016','DD-Mon-YYYY'), Null From Dual; 

We can see now the database has created two additional (hidden) columns for us:

Select column_id, internal_column_id, column_name, hidden_column From user_tab_cols Where table_name='XXEMPLOYEES'; 

So now my query becomes this:

Select full_name, department   From xxemployees As Of Period For effective_date To_Date('01-Jul-2016','DD-Mon-YYYY')  Where person_id=1; 

And sure enough I get the same results. Note though that I can execute Select * From xxemployees and as the effectivity columns are created as hidden by default, we don’t see them. We can still of course select then explicitly. Furthermore there is implicit dealing with Null values for end dates here – so we easily get rid of using a dummy value of 31-Dec-4712 and remove the potential column skew.

We can go a little further too though. Suppose I want to find all the departments that Alice has been between 01-Sep-2016 and now. Again that is very easy using the Versions Period For … Between syntax. As I mentioned above, should I want to see the date validity columns then I can do so.

Select department, effective_date_start, effective_date_end   From xxemployees Versions Period For effective_date Between To_Date('01-Sep-2016','DD-Mon-YYYY') And Trunc(Sysdate)  Where person_id=1; 

But better than that, and what I think is one of the best features of this concept, is that we can very easily implement effective date functionality and remove the need to specify the As Of clause, by using the dbms_flashback_archive.enable_at_valid_time procedure call.

Exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('01-Jul-2016','DD-Mon-YYYY'));  Select *   From xxemployees; 

Unfortunately Oracle Live SQL doesn’t allow you to execute this (which is a shame) however I think it’s pretty self-explanatory so I don’t think I need to do a verbose example. Have a play around with it though in your own instance, there are a number of different options you can use.

• ALL
• ASOF
• CURRENT

No need for tables holding effectivity dates of sessions, developers having to remember whether to use Between or >= … <, worrying about whether values can be null, if not then using dummy values which skew the underlying data and so on. It really is a neat piece of functionality and certainly has a lot of potential for use once more and more customers start moving onto 12c.