0

what is the best way to archive a table with huge amount of data say within 1 year to another table and deleting these records from the existing table?

Currently, i did this:

 /*insert into archive table */
 insert into table_a_archive (select *
  from table_a
 where last_updated < sysdate - interval '1' year);

 /* delete archived data from existing table */
 delete from variable_value where last_updated < sysdate - interval '1' month;

Is there a better approach?

user1746050
  • 2,415
  • 5
  • 20
  • 26
  • you mean older than 1 year rather than within 1 year right? also you don't need the subquery in the second query (just saying where last_updated < sysdate... should work as well) – Jeremy C. Jun 02 '15 at 06:47
  • Why are you using IN clause in Delete statement, you can include your second select criteria to outer WHERE clause, bacause you are using the same table. Actually this is bad idea to delete data from table – The Reason Jun 02 '15 at 06:48
  • have edited it. what's a better approach? – user1746050 Jun 02 '15 at 06:58
  • how many rows you get for year ? – The Reason Jun 02 '15 at 07:00
  • Some abstract tips: consider using of `append` hint and `NOLOGGING` option for insert (tons of articles in the internet, for example http://stackoverflow.com/questions/10092407/optimal-way-to-delete-specified-rows-from-oracle ), consider using of paritioning by date (month for example) on existing table for delete efficiency. – Ilia Maskov Jun 02 '15 at 07:33
  • around 630000 records. – user1746050 Jun 02 '15 at 07:35
  • 1
    I thought you said "a huge amount of data". That is pretty small. How often do you need to do this? – rghome Jun 02 '15 at 07:42
  • I wouldn't use nologging unless you don't mind the risk of losing data. "updated_at" is unlikely to be a good candidate as a partitioning key as well, as it implies the possibility of a row migration when the row is modified in any way. – David Aldridge Jun 02 '15 at 07:48
  • any way to use truncate with the conditions? – user1746050 Jun 02 '15 at 09:57
  • @user1746050 - no, you can't truncate conditionally. However you can truncate one partition/subpartition. Please also bear in mind that delete does not physically free any space. – Rusty Jun 03 '15 at 08:55

1 Answers1

0

1) Create 2 new tables with the same structure as table_a: table_a_archive and table_a_new. (don't forget to grant same privileges, create indexes and etc as on original table_a)

2) Rename table_a to table_a_old

3) Rename table_a_new to table_a

4) Lock table table_a_old (to prevent any changes during migration)

5) Using conditional multi table insert move all data from table_a_old into table_a_archive (when last_updated < sysdate - interval '1' year) and into table_a (else clause) using append hint and parallel

6) commit

7) drop and purge table_a_old

This method requires additional free space and stopping of your application (when rename table package can become invalid, new table during migration will be empty). If you need online solution you can use dbms_redefinition to capture changes occurred during migration.

I can also recommend to consider recreation table as partitioned by RANGE on last_updated instead of having 2 tables.

Rusty
  • 1,988
  • 10
  • 12