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 /
END_DATE
DATE
START_DATE
DATE
COL1
NUMBER
IS_ACTIVE
NUMBER
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>
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
"T"."END_DATE">SYSDATE@!-41))
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;
16385
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;
16384
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;
1
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.
Comments
Post a Comment