Re: partitioning advice?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 16 Jun 2005 07:19:36 +0000 (UTC)
Message-ID: <d8r968$a6a$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"simonc99" <simonc99_member_at_newsguy.com> wrote in message news:d8q19g016qd_at_drn.newsguy.com...
>
> There is no referential integrity, and one sequence generator. All
> referential
> integrity is maintained by the (java / JDBC) application.
>
>
> 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.
>

Since your referential integrity is not in the database, and assuming that you are making old partitions (or at least the tablespaces they are in) read-only, you could do something like the following, adjusting the dates each month:

create parent_clone as
select p.*
from

    parent            p,
    child             c

where

    p.tx_date < 1st Oct
and c.tx_date >=1st Oct -- could allow some arbitrary cut-off forward date
and c.fk_cols = p.pk_cols
;

This gives you the old parents with newer child rows. If you want to preserve the older child rows for these parents so that you have the whole parent/child set, then you could do:

create child_clone as
select c.*
from

    parent_clone p,
    child c
where

    c.tx_date < 1st Oct
and c.fk_cols = p.pk_cols
;

Then

    drop child partition for < 1st Oct (updating global index)     drop parent partition for < 1st Oct (updating global index)     insert into parent select * from parent_clone;     insert into child select * from child_clone;

You need to ensure that the underlying data cannot be changed whilst this is going on, otherwise you could end up with rows in
the clone table that are out of synch with their originals

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005

Received on Thu Jun 16 2005 - 02:19:36 CDT