Menu
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?
332
In temporal databases, valid time (VT) is the time period during which a database fact is valid in the modeled reality. As of December 2011, ISO/IEC 9075, Database Language SQL:2011 Part 2: SQL/Foundation included clauses in table definitions to define "application-time period tables" (that is, valid-time tables).
A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time. Temporal databases could be uni-temporal, bi-temporal or tri-temporal.
Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time.
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
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
