Craig Mullins does a great job of discussing all of the challenges with data archiving in his blog. Most of the issues with data archiving are indeed database agnostic. I'm here today to discuss some of those that are specific to Oracle.
What is the best way to archive data in Oracle? There are many ways to skin that cat, and like all good software developers would respond ... "it depends". When I say "archive", I'm referring to the following things that must happen.
- Data is specified (SQL WHERE clause) that is targeted to be archived.
- That data, and all related data (FK relationships) is extracted and put in some form in which it can be either reloaded into Oracle or queried through some other means. It doesn't do us any good to extract rows from the ORDERS table and skip the rows from the ORDER_LINE_ITEMS table if we ever need to be able to re-create the exact data set.
- Once extracted, the data is then removed from the database. I used the word "removed" and not "deleted" since we may not use a SQL DELETE to actually remove the rows.
Expanding on #3 above, how would we actually remove the rows? It depends. ;-)
If our table is partitioned and the partition key is the same as our archive WHERE clause, then life is good. We simply drop the partition and it doesn't get any faster than that! Each partition would be in its own tablespace (with its own datafiles) and we could copy those to tape and restore them at any time. Archiving becomes quite easy.
If our table is not partitioned, then it depends on the percentage of rows in the table that need archived. If a large percentage of rows we could CREATE TABLE AS SELECT (the rows we want to keep), drop the old table and rename the new to the old. No rollback needed, no massive delete operation, etc.
If our table is not partitioned and it's a small percentage of rows in the table, well ... now we're limited to a SQL DELETE.
You can see how easy archiving is when our data is partitioned accordingly. Those that take time and design table partitions with archiving requirements in mind are rewarded with easy administration.
Tune in next time to find out what housekeeping tasks need performed on a table after a large delete operation is performed.