Re: partitions : truncate/rename OR drop/recreate

In article <AsE%6.5473$YS5.595566_at_afrodite.telenet-ops.be>, "koert54" says...
>
>Hi,
>
>We're going to implement a partitioned table containing historical data. The
>table will contain 24 months of data - or 24 partitions. Now as we roll over
>and start storing our '25th' month of
>data, what is the best way handle this :
>1. drop the oldest partition and recreate it
>2. truncate the oldest partition and rename it
>

3) something entirely different

>I know that if you drop a partition you'll invalidate any global index and
>any local index will also
>be dropped ... but beside that, are there any other consequences I should
>keep an eye on ?
>

global indexes and sliding windows are mutually exclusive. Don't use them on this table. Using LOCAL indexes only.

>Kind Regards,
>Kurt
>

Here is an example of s sliding window:

tkyte_at_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_at_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_at_TKYTE816> create table fy_1999 ( timestamp date, id int ); Table created.

tkyte_at_TKYTE816> create index fy_1999_idx on fy_1999(id)   2 /
Index created.

tkyte_at_TKYTE816> create table fy_2001 ( timestamp date, id int ); Table created.

load fy-2001-here

tkyte_at_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_at_TKYTE816> alter table partitioned
  2 exchange partition fy_1999
  3 with table fy_1999
  4 including indexes
  5 without validation
  6 /
Table altered.

tkyte_at_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_at_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_at_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.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp