Temporal validity - pointless new syntax or something quite useful?

I saw Julian Dyke briefly dicusss this topic at the UKOUG this year and I immediately thought this might give us a performance benefit as we have a lot of time based data with start and end dates.

My first thought was this - if this new 'column' that is being added would somehow be stored in some special format that would allow indexing of it to rapidly find the answers to queries where previously we would have to range scans on the data for > and < for the start and end date of data.

Lets recap on what this new syntax looks like

SQL> create table test (
  2  col1 number,
  3  start_date date,
  4  end_date date,
  5  period for is_active(start_date,end_date)
  6  )
  7  /

So here the new bit is the 'is_active' column which defines the start_date column and the end_date column as the beginning and end of when the data is active - in the case above if we have a start date of 1/1/2010 and an end date of 1/1/2011 then the whole year in between those dates the data is active.

Lets have a look what's been created in the dicyionary

SQL> select COLUMN_NAME,DATA_TYPE from dba_tab_cols where table_name='TEST'
  2  /




So it seems the column is there defined as a 'number' - however we can't see it

SQL> select is_active from test;
select is_active from test
ERROR at line 1:
ORA-00904: "IS_ACTIVE": invalid identifier

Lets insert some test data

SQL> insert into test values (1,sysdate-10,sysdate+9);

1 row created.

SQL> insert into test select * from test;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.


SQL> insert into test values (2,sysdate-99,sysdate-40);

1 row created.

SQL> commit;

Commit complete.

So most of that data is the same date range and only the last value is valid further back in the past. The new functionality enables a different way to query the data

SQL> select * from test as of period for is_active sysdate -41;
         2 15-SEP-13          13-NOV-13

So show me the rows where they were 'active' 41 days ago - just returns that one row. However the plan just looks 'normal'

SQL>  select * from table(dbms_xplan.display_cursor);
SQL_ID  172yudnk5bpq4, child number 1
 select * from test as of period for is_active sysdate -41

Plan hash value: 1357081020

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    19 |    17   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   1 - filter(("T"."START_DATE"<=SYSDATE@!-41 AND

It has just turned the new syntax into the old optimizer way of finding the data. So it seems that this is just a syntax enhancement.......

Or is it.......

Reading on a bit further there is also something quite useful that makes use of this

Lets see the row count to start with

select count(*) from test;

Which is all the rows i inserted - now what happens when i do this:

exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

This only shows rows where the rows are valid 'now'

SQL> select count(*) from test;

SO the row that was valid many weeks ago 'disappears' from the table.

also we have

 exec dbms_flashback_archive.enable_at_valid_time('ASOF',systimestamp -44);

to only show rows that were valid 44 days ago

SQL> select count(*) from test;

SQL> select * from test;
         2 15-SEP-13          13-NOV-13

Here we can just see the row with old date ranges in....

To revert to the normal view

exec dbms_flashback_archive.enable_at_valid_time('ALL');

That's all quite neat and I could see this would actually be useful in warehouses for Type 2 SLowly changing dimensions, or other data where rows want to not be visible based on date ranges.


Post a Comment