Here is an example of s sliding window:

tkyte@TKYTE816> CREATE TABLE partitioned
2 ( timestamp date,
3 id int
4 )
5 PARTITION BY RANGE (timestamp)
6 (
7 PARTITION fy_1999 VALUES LESS THAN
8 ( to_date('01-jan-2000','dd-mon-yyyy') ) ,
9 PARTITION fy_2000 VALUES LESS THAN
10 ( to_date('01-jan-2001','dd-mon-yyyy') ) ,
11 PARTITION the_rest VALUES LESS THAN
12 ( maxvalue )
13 )
14 /
Table created.

tkyte@TKYTE816> create index partitioned_idx_local
2 on partitioned(id)
3 LOCAL
4 /
Index created.

So, this sets up our 'warehouse' table. The data is partitioned by fiscal year
and we have the last two years worth of data online. Notice that I left an empty
partition THE_REST at the end of the table. This will facilitate sliding new
data in quickly. Now, it is the end of the year and we would like to:

1. Remove the oldest fiscal year data. We do not want to lose this data forever,
we just want to age it out and archive it.
2. Add the newest fiscal year data. It will take a while to load it, transform
it, index it, and so on. We would like to do this work without impacting the
availability of the current data if at all possible.

The steps I might take would be:

tkyte@TKYTE816> create table fy_1999 ( timestamp date, id int );
Table created.

tkyte@TKYTE816> create index fy_1999_idx on fy_1999(id)
2 /
Index created.

tkyte@TKYTE816> create table fy_2001 ( timestamp date, id int );
Table created.

load fy-2001-here

tkyte@TKYTE816> create index fy_2001_idx on fy_2001(id) nologging
2 /
Index created.

What I've done here is to set up an empty 'shell' table and index for the oldest
data. What we will do is turn the current full partition into an empty partition
and create a 'full' table, with the FY 1999 data in it. Also, I've completed all
of the work necessary to have the FY 2001 data ready to go. This would have
involved verifying the data, transforming it – whatever complex tasks you need
to undertake to get it ready.

Now we are ready to update the 'live' data:

tkyte@TKYTE816> alter table partitioned
2 exchange partition fy_1999
3 with table fy_1999
4 including indexes
5 without validation
6 /
Table altered.

tkyte@TKYTE816> alter table partitioned
2 drop partition fy_1999
3 /
Table altered.

That's it for aging the old data out. We turned the partition into a full table
and the empty table into a partition. This was a simple data dictionary update –
no large amount of IO took place, it just happened. We can now export that table
(perhaps using a transportable tablespace) out of our database for archival
purposes. We could re-attach it quickly if we ever need to.

Next, we want to slide in the new data:

tkyte@TKYTE816> alter table partitioned
2 split partition the_rest
3 at ( to_date('01-jan-2002','dd-mon-yyyy') )
4 into ( partition fy_2001, partition the_rest )
5 /
Table altered.

tkyte@TKYTE816> alter table partitioned
2 exchange partition fy_2001
3 with table fy_2001
4 including indexes
5 without validation
6 /
Table altered.

That's it for sliding the data in. Again, this was instantaneous – a simple data
dictionary update. Splitting the empty partition takes very little real-time
since there never was, and never will be data in it. That is why I placed an
extra empty partition at the end of the table, to facilitate the split. Then, we
exchange the newly created empty partition with the full table and the full
table with an empty partition. The new data is online.


So, that is how to slide off old data, slide in new data -- very fast, no deletes.


You can definitely script that as well.

A big issue you might have to watch out for -- make sure you use LOCALLY partitioned indexes only. That means no unique constraints across partitions that don't include the TIMESTAMP column. Otherwise you have to rebuild th global indexes after the partition operations.