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)
Lczinkoc's Blog 
Leave a comment