Re: partitioning advice?

simonc99 schrieb:
> 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...
>

Unfortunately you would not be able to realize your model with referential integrity ( at least till 9206 ), there is no possibility to   drop nonempty partitions with enabled referential integrity constraints - ORA-02266 will be raised, so , i suppose, you should do your checks manually (jdbc).

Best regards

Maxim Received on Wed Jun 15 2005 - 16:33:18 CDT