Monday, November 18, 2013

Oracle 12c Temporal Validity time periods in tables.

In Oracle 12c there is new feature called "Temporal Validity". With it you can create time periods between two columns and use these periods for queries.

Example:
-Create table with Temporal Validity period
(you can also add PERIOD FOR in existing table with "ALTER TABLE" clause):
CREATE TABLE test_table(
  test_id NUMBER,
  test_name VARCHAR2(32),
  starting_time TIMESTAMP,
  ending_time TIMESTAMP,
PERIOD FOR testing_time (starting_time, ending_time));

-Inserts are working just like before (PERIOD FOR is not column)
(There can be also NULL values if table constraints accept those.):
INSERT INTO test_table VALUES (1110, 'Test_1', '12-Oct-13', '15-Oct-13');
INSERT INTO test_table VALUES (1110, 'Test_1', '14-Oct-13', null);

- PERIOD FOR gives more variety for your queries (but you can also query table without it):
-This will return all rows that got given date in their time period (first example row):
SELECT * from test_table AS OF PERIOD FOR testing_time TO_TIMESTAMP('13-Oct-13');

-You can also use Period For in "BETWEEN" clause. This will return both example rows.:
SELECT * from test_table VERSIONS PERIOD FOR testing_time BETWEEN
TO_TIMESTAMP('13-Oct-13') AND TO_TIMESTAMP('16-Oct-13');


NOTE: Flashback Query has been extended to support queries on Temporal Validity dimensions. 

You can find more info about Temporal Validity from here:
Oracle 12c New Features
 and here:
Oracle 12c Desing Basics

No comments:

Post a Comment