How to use temporal validity in Oracle 12c?

Question

How to design a table in Oracle 11g so that it is compatible later on with the new "Temporal validity" feature in Oracle 12c?

The online documentation of Oracle 12c specifies how to define temporal validity inthe SQL Language guide (http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#CJADHJHB)

ALTER TABLE my_table ADD (PERIOD FOR my_valid_time (my_valid_start, my_valid_end) );

So one could use the good old valid_from and valid_till columns already in 11g and beef them up to proper periods in 12c, right?

I've inherited databases that use fixed magic dates for "since always" and "for ever", for instance DATE '1900-01-01' and DATE '3999-12-31'. Apparently, 12c uses NULL instead.

So, do we have to abandon using fixed magic dates and switch to NULL dates?

Answer 1

Yes, you will be able to ALTER the table in 12c to enable Temporal Validity (see the ALTER TABLE section of the docs: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_3001.htm#CJAEGCFI)

It works by converting your where clause to a "<=" and "> or is null" clause, so you don't need to change the fixed dates if you don't want to.

Tom Kyte just posted on his blog about this today, with some execellent examples: http://tkyte.blogspot.com/2013/07/12c-flashforward-flashback-or-see-it-as.html