12c - flashforward, flashback or see it as of now...
Posted
by noreply(at)blogger.com (Thomas Kyte)
on Oracle Blogs
See other posts from Oracle Blogs
or by noreply(at)blogger.com (Thomas Kyte)
Published on Wed, 3 Jul 2013 15:06:36 +0000
Indexed on
2013/07/03
17:11 UTC
Read the original article
Hit count: 278
Filed under:
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.
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.
© Oracle Blogs or respective owner
