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