archiving

[penguin71]

I have a situation where I have several tables, each table has CREATED_DATE fields and they FK relationships between the tables:

Example: TableA has FK in TableB which has FK in TableC

We need to archive the records, but only once the CREATED_DATE on the youngest child record (TableC) is older than 30 days old.

Any ideas how this could be accomplished via partitions or some other way??

[PAVB]

Not enough information.
I hope CREATED_DATE is not your PK/FK column.
How about defining "archiving"? is archived data supposed to stay in the database forever?
How about the cardinality of these FK relationships?
How about volume?
Are you planning to "archive" in a daily basis? weekly? monthly?

[ixion]

I have a situation where I have several tables, each table has CREATED_DATE fields and they FK relationships between the tables:

Example: TableA has FK in TableB which has FK in TableC

We need to archive the records, but only once the CREATED_DATE on the youngest child record (TableC) is older than 30 days old.

Any ideas how this could be accomplished via partitions or some other way??

Check out IxUnload, at ixionsoftware.com

[penguin71]

Not enough information.
I hope CREATED_DATE is not your PK/FK column.
How about defining "archiving"? is archived data supposed to stay in the database forever?
How about the cardinality of these FK relationships?
How about volume?
Are you planning to "archive" in a daily basis? weekly? monthly?

create_DATE is not the PK/FK. We have regular sequences for our keys.

By archiving I mean that we will move records from primary schema to a archive schema or db, then delete from primary schema.

The parent to child cardinality is 1 to many in all cases. The hierarchy is about 8 tables deep.

The top parent table gets currently about 5,000 new records a day which will probably rise to about 20,000 per day within the next 3 months. The children multiply from there.

Archive frequency has not been decided, sufficed to say we want to keep our load on the primary db/schema to < 90 days. Parent cannot be archived until all the children are archived.

[ixion]

Use partitioning.

Partition by whatever frequency, week/day/month etc...

When you choose to archive, simply exchange the partitions for all of the tables involved. A good partition naming convention is very helpful for doing this activity along with some custom pl/sql and dba_jobs for scheduling.

If your moving to a seperate DB, then you'll need to Unload the data and re-load it, exp/import, sqlldr, data pump...

Why not extract the partitions using a utility and then make the "archived" data available via external tables? That way you can move them to tape or some other long term storage media.