In the last post, I discussed that how using the In-Database Archiving, you can manage the rows being marked as Active or Inactive which can be very handy and useful to control the display of the inactive rows and also about their participation in the DML’s even when they are not moved anywhere and would keep on existing in the same table itself. In this post, we shall see one more enhancement in the ILM management given in 12c with Temporal Validity.
Concept
In many businesses, the idea of making a record marked as non-required is often based on the business rules which are defined by the application or by the business people. For example, if you are an employee and going to leave your current company to join somewhere else, you would need an experience letter. Now the experience letter must mark your tenure from your date of hiring and not from that date when your details were keyed in the system and you were issued a badge and an official email. The same is going to be applied for an insurance policy which must mark the tenure of the policy expiry from the date you signed it and not from the date the insurance agent has sent it to the main branch to get it entered in the system. So the validity of the data is actually from the business point of view using the date which is important for the business. To get it done, usually developers add columns denoting such “start” and “end” dates in separate columns. From 12c, this is built-in right in the database itself and is called, well no prizes for the right guess-Temporal Validity!
New Terms which needs to be understood
There are few terms that are going to be required to be understood before you start making tables with the temporal validity support and playing with the data and the terms are,
- Valid Time
- Transaction Time
Valid Time
This is the date and time which is termed by the business as important. For example, if you have signed the bond for your car insurance on today i.e. 02-July-2013, this is the Valid time for your insurance and from today it would be termed as effective and would be expired according to the business rules, like for an insurance the tenure would be a year.
Transaction Time
This is the date and time when the required data is actually entered in the database. Since its a part of the database, the management of this time is done by the database by its own internal mechanics like SCN. Because this is an internally managed component, you can use this also to query the data but to the level the db would allow. To use Transaction Time in the queries was already there in the database using the Flashback clauses but now, using the Valid Time, businesses can decide that when the data is considered Valid according to their norms.
Period For clause
To implement Temporal Validity(TV), 12c offers the option to have two date columns in that table which is having TV enabled using the new clause Period For in the Create Table for the newly created tables or in the Alter Table for the existing ones. The columns that are used can be defined while creating the table itself and will be used in the Period For clause or you can skip having them in the table’s definition in the case of which, the Period For clause would be creating them internally. Let’s see both the create table definitions in action,
|
1 2 3 4 5 |
SQL> create table new_emp ( empno number, first_name varchar2(20), last_name varchar2(20), hired date, relieved date, period for user_time(hired, relieved)); Table created. SQL> |
In the above statement, the table NEW_EMP has already got two columns HIRED and RELIEVED which are further used in the PERIOD FOR clause. Since the columns were present in the table’s definition right from the beginning, they would be displayed while describing the table as well as we can see below,
|
1 2 3 4 5 6 7 8 |
SQL> desc new_emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER FIRST_NAME VARCHAR2(20) LAST_NAME VARCHAR2(20) HIRED TIMESTAMP(6) RELIEVED |
Now we shall create a table in which there won’t be any date/time specific columns(for example, an app developer doesn’t want the hiring date and termination date shown to the user describing the table) but would have a Period For clause.
|
1 2 3 |
SQL>create table new_emp1 ( empno number, first_name varchar2(20), last_name varchar2(20), period for user_time); Table created. |
We can see that the table is created but there are no date-time columns present in the description of the table which is understandable as there were none present in the definition of the table when it was created.
|
1 2 3 4 5 6 |
SQL> desc new_emp1 Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER FIRST_NAME VARCHAR2(20) LAST_NAME VARCHAR2(20) |
But what do we have in the DBA_TAB_COLS view?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> select column_name, table_name from dba_tab_cols where table_name='NEW_EMP1' 2 ; COLUMN_NAME TABLE_NAME ---------------------------------------- ---------------------------------------- LAST_NAME NEW_EMP1 FIRST_NAME NEW_EMP1 EMPNO NEW_EMP1 USER_TIME NEW_EMP1 USER_TIME_END NEW_EMP1 USER_TIME_START NEW_EMP1 6 rows selected. |
We can see that db has created for us new implicitly created columns USER_TIME_START and USER_TIME_END usin the prefix USER_TIME which we mentioned in the PERIOD FOR clause. The clause used USER_TIME is NOT any keyword and to demonstrate the same, let’s recreate the same table with a new valid-time dimension name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> create table new_emp1 ( empno number, first_name varchar2(20), last_name varchar2(20), period for user_t); Table created. SQL> select column_name, table_name from dba_tab_cols where table_name='NEW_EMP1' 2 ; COLUMN_NAME TABLE_NAME ---------------------------------------- ---------------------------------------- LAST_NAME NEW_EMP1 FIRST_NAME NEW_EMP1 EMPNO NEW_EMP1 USER_T NEW_EMP1 USER_T_END NEW_EMP1 USER_T_START NEW_EMP1 6 rows selected. SQL> |
You can see that the name of the columns are now using the prefix that we have mentioned.
If you have noticed carefully, there is also one additional hidden column that got created with the name USER_T (or USER_TIME as per the last table’s definition) and this column would be actually used by us in selecting the table based on the valid time and thus can be called valid-time dimension. Not just for the selection of the data but if in the future, you won’t want to have the table using the TV feature, you can use the name of the valid-time dimension and drop it as well.
To implement the TV, there is an implicit Check constraint also gets created in the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> l 1 select c.constraint_name, c.constraint_type, d.column_name 2 from dba_constraints c, dba_Cons_columns d 3 where c.constraint_name=d.constraint_name 4* and c.table_name='NEW_EMP' and c.owner='SYSTEM' SQL> / CONSTRAINT_NAME C COLUMN_NAME -------------------- - -------------------- USER_TIME9FA2B3 C USER_TIME USER_TIME9FA2B3 C HIRED USER_TIME9FA2B3 C RELIEVED |
Temporal Validity in action
So we have understood by now that how to create a table supporting the TV but the important question is how do we use it in real world? If you thought so too, the answer is using the new AS OF clause. To understand it, let’s enter some values in the table NEW_EMP.
|
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 |
SQL>insert into new_emp values(2,'gupta','tina','01-jan-98','01-jun-99'); insert into new_emp values(3,'aujla','gupsy','01-dec-01','01-jun-03'); insert into new_emp values(4,'sidhu','adeep','01-jul-02','01-jun-13'); insert into new_emp values(5,'goel','ankit','01-jan-02','01-dec-13'); insert into new_emp values(6,'bhatia','neeraj','01-jan-05','01-dec-13'); insert into new_emp values(7,'foo','bar','01-jan-06','01-dec-07'); 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> commit; Commit complete. SQL> |
Adn this is how the table is looking finally!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select first_name, hired, relieved from new_emp 2 / FIRST_NAME HIRED RELIEVED -------------------- --------- --------- gupta 01-JAN-98 01-JUN-99 aujla 01-DEC-01 01-JUN-03 sidhu 01-JUL-02 01-JUN-13 goel 01-JAN-02 01-DEC-13 bhatia 01-JAN-05 01-DEC-13 foo 01-JAN-06 01-DEC-07 6 rows selected. SQL> |
Now.let’s try an AS OF PERIOD clause. I shall explain what it did in a moment.
|
1 2 3 4 5 6 7 |
SQL> select first_name , hired, relieved 2 from new_emp 3 as of period for user_time to_date('01-dec-01','dd-mon-yy'); FIRST_NAME HIRED RELIEVED -------------------- -------------------- -------------------- aujla 01-DEC-01 01-JUN-03 |
So what is happening is that Oracle is going to search the mentioned date, 01-DEC-01 between the valid time range columns which in our case are HIRED and RELIEVED. You can confirm this by looking at the execution plan of the query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> set autot trace exp /SQL> Execution Plan ---------------------------------------------------------- Plan hash value: 2362416596 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| NEW_EMP | 1 | 30 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("T"."HIRED" IS NULL OR "T"."HIRED"<=TO_DATE('01-dec-01','dd-mon-yy')) AND ("T"."RELIEVED" IS NULL OR "T"."RELIEVED">TO_DATE('01-dec-01','dd-mon-yy'))) Note ----- - dynamic statistics used: dynamic sampling (level=2) |
So we can see that Oracle is applying a filter which is limiting the data using a Between AND clause over the date-time columns of our table HIRED & RELIEVED.
Now let’s modify the query for a different date.
|
1 2 3 4 5 6 7 8 |
SQL> select first_name , hired, relieved 2 from new_emp 3 as of period for user_time to_date('01-mar-02','dd-mon-yy'); FIRST_NAME HIRED RELIEVED -------------------- -------------------- -------------------- aujla 01-DEC-01 01-JUN-03 goel 01-JAN-02 01-DEC-13 |
You can see that now the output is including the rows that are within the range corresponding to this given date of ours. Let’s use different date values and see what’s the result is going to be.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select first_name , hired, relieved from new_emp as of period for user_time to_date('01-jan-03','dd-mon-yy'); 2 3 FIRST_NAME HIRED RELIEVED -------------------- -------------------- -------------------- aujla 01-DEC-01 01-JUN-03 sidhu 01-JUL-02 01-JUN-13 goel 01-JAN-02 01-DEC-13 SQL> select first_name , hired, relieved 2 from new_emp 3 as of period for user_time to_date('01-jun-13','dd-mon-yy'); FIRST_NAME HIRED RELIEVED -------------------- -------------------- -------------------- goel 01-JAN-02 01-DEC-13 bhatia 01-JAN-05 01-DEC-13 |
As expected, the results are based on the given value and its occurance in the columns HIRED & RELIEVED.Now, we shall use that date which is not there in our table.
|
1 2 3 4 5 6 7 |
SQL> select first_name , hired, relieved from new_emp as of period for user_time to_date('01-jan-14','dd-mon-yy'); 2 3 no rows selected SQL> |
Obviously, there is no match found for this date in the table and we got no output.
So all what you, as a business user would need to do is to write a query with his desired date expression and Oracle would evaluate the existing data with it and based on the comparison results, the rows would be displayed and would be treated as Current or not displayed and would be treated as Non-Current. Note that the terms Current and Non-Current are actually my own terms over here to explain the concept.
Using the DBMS_FLASHBACK.ENABLE_AT_VALID_TIME procedure
We saw above that how we can use the expression to control the output of the result. There is another way to do it as well and that is to use the procedure ENABLE_AT_VALID_TIME of the package DBMS_FLASHBACK_ARCHIVE. This procedure lets you see ALL the rows of the table(default) or CURRENT rows i.e the rows which are considered to be valid. The last option for the procedure is ASOF which is similar to what the AS OF clause does when used in the query itself. For example,
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF','01-jan-13'); PL/SQL procedure successfully completed. SQL> select first_name, hired, relieved from new_emp; FIRST_NAME HIRED RELIEVED -------------------- --------- --------- sidhu 01-JUL-02 01-JUN-13 goel 01-JAN-02 01-DEC-13 bhatia 01-JAN-05 01-DEC-13 |
Note:- I couldn’t make the values CURRENT and ALL to work properly or may be I am lacking the understanding about them somewhere. For me, the CURRENT works in the same session when the table is initially created and not afterwards. Have a look,
|
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 |
SQL> CREATE TABLE my_emp( empno NUMBER, last_name VARCHAR2(30), start_time TIMESTAMP, end_time TIMESTAMP, PERIOD FOR user_valid_time (start_time, end_time)); INSERT INTO my_emp VALUES (100, 'Ames', '01-Jan-10', '30-Jun-11'); INSERT INTO my_emp VALUES (101, 'Burton', '01-Jan-11', '30-Jun-11'); INSERT INTO my_emp VALUES (102, 'Chen', '01-Jan-12', null); 2 3 4 5 6 Table created. SQL> SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> SQL> commit; Commit complete. SQL> exec dbms_flashback_archive.enable_at_valid_time('current'); PL/SQL procedure successfully completed. SQL> SELECT * from my_emp; EMPNO LAST_NAME ---------- ------------------------------ START_TIME --------------------------------------------------------------------------- END_TIME --------------------------------------------------------------------------- 102 Chen 01-JAN-12 12.00.00.000000 AM SQL> column start_time format a20 SQL> column end_time format a20 SQL> / EMPNO LAST_NAME START_TIME ---------- ------------------------------ -------------------- END_TIME -------------------- 102 Chen 01-JAN-12 12.00.00.0 00000 AM SQL> set linesize 200 SQL> / EMPNO LAST_NAME START_TIME END_TIME ---------- ------------------------------ -------------------- -------------------- 102 Chen 01-JAN-12 12.00.00.0 00000 AM SQL> exec dbms_flashback_archive.enable_at_valid_time('current'); PL/SQL procedure successfully completed. SQL> SELECT * from my_emp; EMPNO LAST_NAME START_TIME END_TIME ---------- ------------------------------ -------------------- -------------------- 102 Chen 01-JAN-12 12.00.00.0 00000 AM |
So till now, it worked as expected. But see what happens when I end the session and do the login again.
|
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 |
SQL> conn system/oracle Connected. SQL> SELECT * from my_emp; EMPNO LAST_NAME START_TIME END_TIME ---------- ------------------------------ -------------------- -------------------- 100 Ames 01-JAN-10 12.00.00.0 30-JUN-11 12.00.00.0 00000 AM 00000 AM 101 Burton 01-JAN-11 12.00.00.0 30-JUN-11 12.00.00.0 00000 AM 00000 AM 102 Chen 01-JAN-12 12.00.00.0 00000 AM SQL> exec dbms_flashback_archive.enable_at_valid_time('current'); PL/SQL procedure successfully completed. SQL> SELECT * from my_emp; EMPNO LAST_NAME START_TIME END_TIME ---------- ------------------------------ -------------------- -------------------- 100 Ames 01-JAN-10 12.00.00.0 30-JUN-11 12.00.00.0 00000 AM 00000 AM 101 Burton 01-JAN-11 12.00.00.0 30-JUN-11 12.00.00.0 00000 AM 00000 AM 102 Chen 01-JAN-12 12.00.00.0 00000 AM SQL> exec dbms_flashback_archive.enable_at_valid_time('current'); PL/SQL procedure successfully completed. SQL> exec dbms_flashback_archive.enable_at_valid_time('current'); PL/SQL procedure successfully completed. SQL> SELECT * from my_emp; EMPNO LAST_NAME START_TIME END_TIME ---------- ------------------------------ -------------------- -------------------- 100 Ames 01-JAN-10 12.00.00.0 30-JUN-11 12.00.00.0 00000 AM 00000 AM 101 Burton 01-JAN-11 12.00.00.0 30-JUN-11 12.00.00.0 00000 AM 00000 AM 102 Chen 01-JAN-12 12.00.00.0 00000 AM SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options |
So I am not sure what I am missing? If anyone has a better clarity, feel free to put the explanation in the comments and I shall add that here.
Disabling Temporal Validity
As I said somewhere above, if you don’t want to use the TV feature, all what you need to do is to drop the valid-time dimension with the Alter Table command like below,
|
1 2 3 |
SQL> alter table new_emp1 drop(period for user_t); Table altered. |
By this Drop command, if the data-time columns were implicitly created, they would now be removed and the AS OF PERIOD FOR clause won’t work.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> alter table new_emp drop( period for user_time); Table altered. SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF','01-jan-13'); PL/SQL procedure successfully completed. SQL> select first_name, hired, relieved from new_emp; FIRST_NAME HIRED RELIEVED -------------------- --------- --------- t 01-JAN-98 01-JAN-99 gupta 01-JAN-98 01-JUN-99 aujla 01-DEC-01 01-JUN-03 sidhu 01-JUL-02 01-JUN-13 goel 01-JAN-02 01-DEC-13 bhatia 01-JAN-05 01-DEC-13 foo 01-JAN-06 01-DEC-07 7 rows selected. |
So we can see that now even using the PERIOD FOR clause is not making any impact on the result and the entire result is shown from the table which means that the TV feature is now no longer working.
Conclusion
Oracle always had the functionality to move “back” in the time using its Flashback technology. But the requirement to move the data in the “future” was not really there till now. With Temporal Validity, now you can even do the “flashfront” without asking your developers writing some kind of code for it.
References
http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS967
http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14127
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html
http://tkyte.blogspot.com.au/2013/07/12c-flashforward-flashback-or-see-it-as.html
Oracle 12c, ILM Management Part-2, Temporal Validity http://t.co/SqJpKyiblN #DB12c #Oracle cc/ @OracleAlchemist @OsamaOracle