Moving record set

From: Richard Bragg <richard.bragg_at_ntl.com>
Date: Mon, 4 Dec 2000 13:25:28 -0000
Message-ID: <hXMW5.5636$uP1.104440_at_news6-win.server.ntlworld.com>


Some advice please.

We have a small(ish) oracle database split into 2 schemas. The schemas can be thought of as identical. There is a master (parent) table and a set of child tables. Foreign key constraints with a delete cascade is also present.

The first is a small schema collecting data from automatted processes. Records are added and modified 24x7 and are never deleted. At some point the automation will flag the master record that it is completed and no further updates will occur.

We need to move the master record and it's children to the second schema that will act as a store for running large reports against. Once transferred the records are deleted from the first schema.

We do not want to use a trigger as we want to optimise the reporting and not have contention with writes. At some point the second schema may be ported to a separate machine. Any suggestions for a efficient mechanism to peform the transfer. I have tried a perl script that collects the rowid's and primary key values from the master. It then moves the master record and uses that data to move the child records before deleting the master in the first schema but this is too slow as records are being added faster than they can be cleared. (OK the perl is very basic).

Is there perhaps a way of getting parent records in chunks (1000 at a time) to speed things up a tad.

TIA Received on Mon Dec 04 2000 - 14:25:28 CET

Original text of this message