partitioning advice?

Am just about to deploy a new application - one of those 'generic' database types with very little Oracle specific functionality built in.

There is no referential integrity, and one sequence generator. All referential integrity is maintained by the (java / JDBC) application.

I'm looking at using partitioning as the expected data volume is large (> 3TB). A few good partitioning candidates have come to light, and we can range partition by date on those. I've partitioned table, indexes (where available - primary key is GLOBAL) and LOBS in separate date-named tablespaces. I know this could cause issues around SMON and un-reusable datafile names, but we'll have to live with that; the added flexibility and obviousness of the naming scheme makes it worth it.

One of the tables has a child table, which contains a date. However, this could be different from the date within the parent (which it's partitioned on). It's relationship is a unique ID. We wish to keep 12 months of data before removal with partition drops.

The problem is that two separate date range's transaction IDs could appear in the child table. Dropping the parent leaves orphaned data. Blindly dropping the child table's partition could mean data pertaining to (still active) transactions could be lost.

e.g.,

Row inserted into parent, date is 23:59:59 on the 30th September. It sits in September's tablespace.
Child row is inserted 00:00:02 on the 1st October. It sits in October's tablespace.
When the year is up, September's partition is dropped, losing the parent data for the child row.

Any ideas? Can't really think of anything apart from :

Obtaining low and high transaction IDs for the date range we wish to drop in the parent table.
Checking child table partition to be dropped for existence of anything above the high transaction ID. If there is anything, don't drop the partition. If there isn't drop it.

Anyone got any comments, advice, etc??

Many thanks in advance... Received on Wed Jun 15 2005 - 21:57:14 CEST