Temporal validity(history) in Oracle 12c


In Oracle 11g You can store the previous states of the a table in a Flashback Archive.
Now, starting with Oracle 12c, You can store the old and current states of the rows in the same table.
This is extremely important for dimensonal tables in a Datawarehouse,
because we may want to keep the whole history of a dimension table.
(Consider SCD2 Dimensions)
We can explicitly or implicitly define date/timestamp type columns
that are responsible to store the “lifetime” period of a particular row.
Consider the following scenario:
1. We create a table with invisible(hidden) timestamp columns:

CREATE TABLE my_emp_hidden(
  empno NUMBER,
  last_name VARCHAR2(30),
  PERIOD FOR user_valid_time);

2. Let’s see the generated DDL statement for the table:

 
SET LONG 10000
SELECT DBMS_METADATA.GET_DDL('TABLE','MY_EMP_HIDDEN','HR') FROM DUAL;
SQL_SCRIPT
-----------
CREATE TABLE "HR"."MY_EMP_HIDDEN" 
   (	"EMPNO" NUMBER, 
	"LAST_NAME" VARCHAR2(30 BYTE), 
	 CONSTRAINT "USER_VALID_TIME9A523" CHECK (USER_VALID_TIME_START < USER_VALID_TIME_END) ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  ILM ENABLE LIFECYCLE MANAGEMENT  ;

We can observe a check constraint referring to two (invisible) columns:
USER_VALID_TIME_START and USER_VALID_TIME_END
If you want to see the invisible columns then consider
the following SELECT statement (see Julian Dyke’s blog:
http://www.juliandyke.com/Blog/?p=419)

COL NAME FORMAT A22
COL COL# FORMAT 999
SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') PROPERTY
FROM sys.col$
WHERE obj# =
(
  SELECT obj# FROM sys.obj$ WHERE name = 'MY_EMP_HIDDEN'
);
NAME                   COL#    INTCOL#    SEGCOL# PROPERTY    
---------------------- ---- ---------- ---------- -------------
USER_VALID_TIME_START     0          1          1            20 
USER_VALID_TIME_END       0          2          2            20 
USER_VALID_TIME           0          3          0         10028 
EMPNO                     1          4          3             0 
LAST_NAME                 2          5          4             0 

The value of COL# is zero for the invisible columns!
3. Now we populate the table with the following 3 rows:

 
INSERT INTO my_emp_hidden
(empno,last_name,USER_VALID_TIME_START,USER_VALID_TIME_END)
VALUES (100, 'King', to_timestamp('01-Jan-10'), to_timestamp('02-Jun-12'));
INSERT INTO my_emp_hidden
(empno,last_name,USER_VALID_TIME_START,USER_VALID_TIME_END)
VALUES (101, 'Kochhar', to_timestamp('01-Jan-11'), to_timestamp('30-Jun-12'));
INSERT INTO my_emp_hidden
(empno,last_name,USER_VALID_TIME_START,USER_VALID_TIME_END)
VALUES (102, 'De Haan', to_timestamp('01-Jan-12'),NULL);

4. Using the ENABLE_AT_VALID_TIME procedure of DBMS_FLASHBACK_ARCHIVE package
we can see the ALL rows or CURRENT rows only!

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL')
SELECT * FROM my_emp_hidden;
anonymous block completed
     EMPNO LAST_NAME                    
---------- ------------------------------
       100 King                           
       101 Kochhar                        
       102 De Haan                        

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT')
SELECT * FROM my_emp_hidden;
anonymous block completed
     EMPNO LAST_NAME                    
---------- ------------------------------
       102 De Haan                        

We can see only one row in the second scenario, because we have only one current
(visible) row!
For SQL Tuning experts:

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:183
SQL_ID  445uvm4xj0ar8, child number 0
-------------------------------------
SELECT * FROM my_emp_hidden
 
Plan hash value: 764633393
 
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| MY_EMP_HIDDEN |     1 |    60 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter((("T"."USER_VALID_TIME_START" IS NULL OR 
              SYS_EXTRACT_UTC("T"."USER_VALID_TIME_START")<=SYS_EXTRACT_UTC(SYSTIMESTAMP(
              6))) AND ("T"."USER_VALID_TIME_END" IS NULL OR 
              SYS_EXTRACT_UTC("T"."USER_VALID_TIME_END")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6))
              )))
 

5.Valid AS OF PERIOD FOR queries:

-- Returns only King.
SELECT * from my_emp_hidden AS OF PERIOD 
FOR user_valid_time TO_TIMESTAMP('01-Jun-10');
    EMPNO LAST_NAME                    
---------- ------------------------------
       100 King        
-- Returns  King and Kochhar.
SELECT * from my_emp_hidden AS OF PERIOD 
FOR user_valid_time TO_TIMESTAMP('01-Jun-11');
     EMPNO LAST_NAME                    
---------- ------------------------------
       100 King                           
       101 Kochhar                        

-- Returns Kochhar and De Haan
SELECT * from my_emp_hidden AS OF PERIOD 
FOR user_valid_time TO_TIMESTAMP('03-Jun-12');
     EMPNO LAST_NAME                    
---------- ------------------------------
       101 Kochhar                        
       102 De Haan                        
-- Returns all rows
SELECT * from my_emp_hidden VERSIONS PERIOD 
FOR user_valid_time 
BETWEEN TO_TIMESTAMP('02-jun-11') AND TO_TIMESTAMP('01-May-12');
     EMPNO LAST_NAME                    
---------- ------------------------------
       100 King                           
       101 Kochhar                        
       102 De Haan                        
-- Returns no rows
SELECT * from my_emp_hidden AS OF PERIOD 
FOR user_valid_time TO_TIMESTAMP('01-Jun-09');
no rows selected

If you want to explicitly define the time constraint columns for a table,
consider the documentation
(http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS967)
or briefly here:

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT')
CREATE TABLE my_emp(
  empno NUMBER,
  last_name VARCHAR2(30),
  start_time DATE,
  end_time DATE,
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);
 
-- Valid Time Queries --
 
-- AS OF PERIOD FOR queries:
 
-- Returns only Ames.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_DATE('01-Jun-10');

-- Returns  Ames and Burton, but not Chen.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_DATE('01-Jun-11');

-- Returns no one.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_DATE( '01-Jul-11');

-- Returns only Chen.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_DATE('01-Feb-12');
 
-- VERSIONS PERIOD FOR ... BETWEEN queries:
 
-- Returns only Ames.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN 
   TO_DATE('01-Jun-10') AND TO_DATE('02-Jun-10');

-- Returns Ames and Burton.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN 
   TO_DATE('01-Jun-10') AND TO_DATE('01-Mar-11');

-- Returns only Chen.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN 
   TO_DATE('01-Nov-11') AND TO_DATE('01-Mar-12');

-- Returns no one.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN 
   TO_DATE('01-Jul-11') AND TO_DATE('01-Sep-11');
no rows selected
SELECT * FROM MY_EMP;
     EMPNO LAST_NAME                      START_TIME END_TIME
---------- ------------------------------ ---------- ---------
       102 Chen                           01-JAN-12    

.

This feature is VERY IMPORTANT whenever we want to keep previous versions of the rows
in the same table!
(ps: My e-mail changed! The current is:czinkoczkilaszlo@gmail.com)

Advertisements

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


%d bloggers like this: