Hi there
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...
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...
Comment