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.

Here is a quick example, we'll start with a table:

ops$tkyte%ORA12CR1> create table addresses
  2  ( empno       number,
  3    addr_data   varchar2(30),
  4    start_date  date,
  5    end_date    date,
  6    period for valid(start_date,end_date)
  7  )
  8  /

Table created.



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:

ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
  2  values ( 1234, '123 Main Street', trunc(sysdate-5), trunc(sysdate-2) );

1 row created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
  2  values ( 1234, '456 Fleet Street', trunc(sysdate-1), trunc(sysdate+1) );

1 row created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
  2  values ( 1234, '789 1st Ave', trunc(sysdate+2), null );

1 row created.


and you can either see all of the data:

ops$tkyte%ORA12CR1> select * from addresses;

     EMPNO ADDR_DATA                      START_DAT END_DATE
---------- ------------------------------ --------- ---------
      1234 123 Main Street                27-JUN-13 30-JUN-13
      1234 456 Fleet Street               01-JUL-13 03-JUL-13
      1234 789 1st Ave                    04-JUL-13

or query "as of" some point in time - as  you can see in the predicate section - it is just doing a query rewrite to automate the "where" filters:

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate-3;

     EMPNO ADDR_DATA                      START_DAT END_DATE
---------- ------------------------------ --------- ---------
      1234 123 Main Street                27-JUN-13 30-JUN-13

ops$tkyte%ORA12CR1> @plan
ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  cthtvvm0dxvva, child number 0
-------------------------------------
select * from addresses as of period for valid sysdate-3

Plan hash value: 3184888728

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |     1 |    48 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((("T"."START_DATE" IS NULL OR
              "T"."START_DATE"<=SYSDATE@!-3) AND ("T"."END_DATE" IS NULL OR
              "T"."END_DATE">SYSDATE@!-3)))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate;

     EMPNO ADDR_DATA                      START_DAT END_DATE
---------- ------------------------------ --------- ---------
      1234 456 Fleet Street               01-JUL-13 03-JUL-13

ops$tkyte%ORA12CR1> @plan
ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  26ubyhw9hgk7z, child number 0
-------------------------------------
select * from addresses as of period for valid sysdate

Plan hash value: 3184888728

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |     1 |    48 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((("T"."START_DATE" IS NULL OR
              "T"."START_DATE"<=SYSDATE@!) AND ("T"."END_DATE" IS NULL OR
              "T"."END_DATE">SYSDATE@!)))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate+3;

     EMPNO ADDR_DATA                      START_DAT END_DATE
---------- ------------------------------ --------- ---------
      1234 789 1st Ave                    04-JUL-13

ops$tkyte%ORA12CR1> @plan
ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  36bq7shnhc888, child number 0
-------------------------------------
select * from addresses as of period for valid sysdate+3

Plan hash value: 3184888728

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |     1 |    48 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((("T"."START_DATE" IS NULL OR
              "T"."START_DATE"<=SYSDATE@!+3) AND ("T"."END_DATE" IS NULL OR
              "T"."END_DATE">SYSDATE@!+3)))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.


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.
POST A COMMENT

12 Comments:

Blogger Finn Ellebaek Nielsen said....

Seen that the datatype is DATE I assume this works with time part as well so the resolution of this feature is down to the second, not only an entire day?

Wed Jul 03, 08:35:00 AM EDT  

Anonymous Sokrates said....

a. are there migrations tools from workspace manager with valid time support to temporal validity ?

b. how does this feature integrate with flashback data archive ?

Wed Jul 03, 08:50:00 AM EDT  

Blogger Thomas Kyte said....

@Finn,

correct - if I had inserted and queried these rows:


ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
2 values ( 1234, '123 Main Street',
3 to_date( '01-jul-2013 13:02:00', 'dd-mon-yyyy hh24:mi:ss' ),
4 to_date( '07-jul-2013 09:50:00', 'dd-mon-yyyy hh24:mi:ss' ) );

1 row created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
2 values ( 1234, '456 Fleet Street',
3 to_date( '07-jul-2013 09:55:00', 'dd-mon-yyyy hh24:mi:ss' ),
4 to_date( '08-jul-2013 09:50:00', 'dd-mon-yyyy hh24:mi:ss' ) );

1 row created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
2 values ( 1234, '789 1st Ave',
3 to_date( '08-jul-2013 09:50:01', 'dd-mon-yyyy hh24:mi:ss' ),
4 null );

1 row created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> select * from addresses as of period for valid
2 to_date( '07-jul-2013 09:49:49', 'dd-mon-yyyy hh24:mi:ss' );

EMPNO ADDR_DATA START_DAT END_DATE
---------- ------------------------------ --------- ---------
1234 123 Main Street 01-JUL-13 07-JUL-13

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> select * from addresses as of period for valid
2 to_date( '07-jul-2013 09:52:00', 'dd-mon-yyyy hh24:mi:ss' );

no rows selected

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> select * from addresses as of period for valid
2 to_date( '07-jul-2013 09:55:00', 'dd-mon-yyyy hh24:mi:ss' );

EMPNO ADDR_DATA START_DAT END_DATE
---------- ------------------------------ --------- ---------
1234 456 Fleet Street 07-JUL-13 08-JUL-13



you can see (unfortunately the formatting stinks in comments here...) that depending on the minute and second you choose - you get the data that was valid at that point in time.

Wed Jul 03, 10:54:00 AM EDT  

Blogger Thomas Kyte said....

@Sokrates,

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.

Wed Jul 03, 11:05:00 AM EDT  

Anonymous Sokrates said....

Thanks for claryfying things.

One further question:
the following two queries are equivalent, right ?

select *
from t as of timestamp sysdate-2/1440
as of period for valid sysdate - 3/1440


select *
from t as of period for valid sysdate - 3/1440
as of timestamp sysdate-2/1440

Wed Jul 03, 02:29:00 PM EDT  

Blogger Thomas Kyte said....

@Sokrates

flashback dictates when the query runs "as of"

the temporal filter will be applied post flashing back.

so yes, those two are the same, they flashback and then filter.

Wed Jul 03, 02:36:00 PM EDT  

Anonymous Anonymous said....

IT NEVER RAIN IN CALIFORNIA :) ElyBahiana.jimdo.com ( HERSELF )

Thu Jul 04, 03:04:00 AM EDT  

Anonymous Sokrates said....

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#i2126134

seems to suggest to me that you actually are allowed to specify only one "flashback_query_clause" and not both as of timestamp/scn and as of period for.
Or do I misinterpret the diagrams there ?

Thu Jul 04, 11:50:00 AM EDT  

Anonymous Matthias Rogel said....

and keep in mind, this new feature is not nearly as rich as workspace management.

I wonder if it is good for anything at all.
Primary constraints are not supported as it seems - and who wants a table without a primary key ? - :



create table addresses
(
empno number,
addr_data varchar2(30),
start_date date,
end_date date,
period for valid(start_date,end_date),
constraint ad_pk primary key(empno)
);

insert into addresses(empno, addr_data, start_date, end_date)
values(1234, '123 Main Street', date'2013-06-28', date'2013-07-01');

insert into addresses(empno, addr_data, start_date, end_date)
values(1234, '456 Fleet Street', date'2013-07-02', date'2013-07-04');

2 insert into addresses(empno, addr_data, start_date, end_date)
*
ERROR at line 1:
ORA-00001: unique constraint (SOKRATES.AD_PK) violated




whereas


create table add_wm
(
empno number,
addr_data varchar2(30),
constraint adw_pk primary key(empno)
);

exec dbms_wm.enableversioning(table_name => 'ADD_WM', validTime=>true)

sokrates@12.1 > insert into add_wm(empno, addr_data, wm_valid )
values(1234, '123 Main Street',
wm_period(date'2013-06-28', date'2013-07-01')
); 2 3 4

1 row created.

sokrates@12.1 > insert into add_wm(empno, addr_data, wm_valid )
values(1234, '456 Fleet Street',
wm_period(date'2013-07-02', date'2013-07-04')
);
2 3 4
1 row created.


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 -
)

Tue Jul 09, 10:16:00 AM EDT  

Blogger eric said....

Maybe not related but i have following question...please reply

I used REGEXP_REPLACE to delete Scott. is there any way to delete Scott and Storage information same time using REGEXP_REPLACE...?

select REGEXP_REPLACE(dbms_metadata.get_ddl('TABLE','EMP'),'("Scott".)','', 1, 0, 'i') from dual;

result:

CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"SAL" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"

Thu Jul 11, 12:35:00 PM EDT  

Blogger Thomas Kyte said....

@eric

read http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30802454515375#65175499711182

and just use replace - no need for regular expressions.

Fri Jul 12, 02:00:00 AM EDT  

Blogger Ariel Machado said....

@Matthias,

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 :



CREATE TABLE addresses(empno NUMBER,
addr_data VARCHAR2(30),
start_date DATE,
end_date DATE,
period FOR valid(start_date, end_date),
del_status NUMBER(6),
CONSTRAINT ad_pk primary key(empno, del_status));

CREATE OR REPLACE PROCEDURE address_entry
(
i_empno IN addresses.empno%TYPE,
i_addr_data IN addresses.addr_data%TYPE,
i_delete_entry IN BOOLEAN DEFAULT FALSE
) IS

l_current_status addresses.del_status%TYPE;
l_del_status addresses.del_status%TYPE;
l_timestamp DATE;
BEGIN
l_timestamp := SYSDATE;

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 i_delete_entry = TRUE
THEN
--A delete: del_status is negative
l_del_status := - (abs(l_current_status + 1));

ELSE
IF l_current_status IS NULL
THEN
--A new entry: snapshots start at +10
l_del_status := 10;

ELSE
--An update: del_status is positive
l_del_status := abs(l_current_status) + 1;
END IF;
END IF;

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;

END IF;

-- Save new entry version
INSERT INTO addresses
(empno, del_status, addr_data, start_date, end_date)
VALUES
(i_empno, l_del_status, i_addr_data, l_timestamp, NULL);

END address_entry;
/


BEGIN
address_entry(i_empno => 1234, i_addr_data => '123 Main Street');
END;
/

BEGIN
address_entry(i_empno => 1234, i_addr_data => '456 Fleet Street');
END;
/

BEGIN
address_entry(i_empno => 1234, i_addr_data => '456 Fleet Street', i_delete_entry => TRUE);
END;
/
-- Valid entries
SELECT *
FROM addresses a
WHERE a.end_date IS NULL
AND a.del_status >= 0;


-- Valid entries (including deleted entries)
SELECT *
FROM addresses a
WHERE a.end_date IS NULL;


-- Full history of entries
SELECT a.*,
CASE
WHEN del_status = 10 THEN
'New'
WHEN del_status < 0 THEN
'Delete'
ELSE
'Update'
END operation
FROM addresses a
ORDER BY a.empno, abs(a.del_status);

Wed Aug 14, 05:22:00 AM EDT  

POST A COMMENT

<< Home