Wednesday, July 03, 2013

12c - flashforward, flashback or see it as of now...

Oracle 9i exposed flashback query to developers for the first time. The ability to flashback query dates back to version 4 however (it just wasn't exposed). Every time you run a query in Oracle it is in fact a flashback query - it is what multi-versioning is all about.

However, there was never a flashforward query (well, ok, the workspace manager has this capability - but with lots of extra baggage). We've never been able to ask a table "what will you look like tomorrow" - but now we do.

The capability is called Temporal Validity. If you have a table with data that is effective dated - has a "start date" and "end date" column in it - we can now query it using flashback query like syntax. The twist is - the date we "flashback" to can be in the future. It works by rewriting the query to transparently the necessary where clause and filter out the right rows for the right period of time - and since you can have records whose start date is in the future - you can query a table and see what it would look like at some future time.

the new bit is on line 6 (it can be altered into an existing table - so any table you have with a start/end date column will be a candidate). The keyword is PERIOD, valid is an identifier I chose - it could have been foobar, valid just sounds nice in the query later. You identify the columns in your table - or we can create them for you if they don't exist. Then you just create some data:

All in all a nice, easy way to query effective dated information as of a point in time without a complex where clause. You need to maintain the data - it isn't that a delete will turn into an update the end dates a record or anything - but if you have tables with start/end dates, this will make it much easier to query them.

*Note added 4-jul-2013: this feature currently is not supported/working with the pluggable database infrastructure. This is a temporary limitation.

a) no, not in particular, it would be a rebuild of the table from the _hist view as a create table as select using the created and retired columns as your start/end dates.

and keep in mind, this new feature is not nearly as rich as workspace management. On the other hand, it doesn't have quite the overhead of workspace manager either

b) the two are similar in nature, but not really complimentary. Flashback archive is designed to be a tamper proof history - the old versions of data maintained out of line. You query the table and it'll be as of "right now". You can flashback query to see old versions.

With this temporal validity - it is sort of a partial "do it yourself" flashback archive. It is not tamper proof however. In order to see the data as of right now - you have to ask for the "right now" data.

They are similar but not really integrated in any fashion. You can use the archive on a 'temporal' table however to audit all changes (to make sure someone isn't changing history for example). The flashback would only have pre-updated images of updated rows so any time you end dated a record or modified a records content, you'd have a trail of those changes.

As long as the date ranges do not overlap, we can add here more rows with the same primary key ( thus solving http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:474221407101 simply with a constraint ! - at least for datatypes DATE / TIMESTAMP -)

You can use this and "emulate" the behavior of Workspace Manager using the similar approach that dbms_wm.enableversioning does: add a field to track version changes in entries and adding this field to your original PK creating and compound PK :

BEGIN --Retrieve last entry edition SELECT a.del_status INTO l_current_status FROM addresses a WHERE a.empno = i_empno AND a.end_date IS NULL; EXCEPTION WHEN no_data_found THEN IF i_delete_entry THEN -- we cannot delete an unexistent entry RETURN; ELSE --Is a new entry l_current_status := NULL; END IF; END;

IF l_current_status IS NOT NULL THEN -- Make previous entry as outdated filling retire_time field UPDATE addresses a SET a.end_date = l_timestamp WHERE a.empno = i_empno AND a.del_status = l_current_status;

About Me

The views expressed are my own and not necessarily those of Oracle and its affiliates. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.
I've been using Oracle since 1988. I've been working at Oracle since 1993 (version 7.0). I spend way too much time working on asktom.oracle.com...