Re: HELP! --- Best way to delete large amounts of data.

I see 2 performance wise stupid things here. 1. Delete of the group of records requires about 8 times less time than cursor delete of individual records.
2. Child table is bigger, apparently, it should be driving table in this process.

Try this.

1. Archiving

insert into <child_archive_table> as
select * from <child_table>
where <conditions>;

insert into <master_archive_table> as
select * from <master_table>
where <conditions>;

2. Deletes.

delete from <child_table>
where <conditions>;

commit;

delete from <master_table>
where <conditions>;

commit;

Possible implementation problems:
1. Rollback segment too small. It can happen on delete stage. Solution: increase size of rollback segment. commits between deletes help a little here.
2. Conditions for child table can require subquery on master table. It can be helped by tuning those statements.

The main thing to remember: SQL is a SET language. It works the best when operating with sets, not with individual records.

In article <91oomd$33q$1_at_autumn.news.rcn.net>,   "Nick Bowler" <nbowler_at_erols.com> wrote:
> I have been given this problem, and the DBA is out on vacation. Hopefully
> someone has had to deal with this before and can help.
>
> I have a large database (25 million records) on a AIX box.
> I need to archive and then delete about 5 million of the records.
> There are plenty of child records that also need to be archived and
 deleted
>
> The archive program I inherited takes about 3 days to run -- way too long.
> However, it is logically correct. It opens a cursor on the main table for
> all records to archive / delete. For each record to be archived /
 deleted,
> it finds all the children, then archives / deletes them. Finally, after a
> given number of records on the main table have been processed, it issues a
> commit. This process is restartable, and, frankly, is what I would have
> written if I had to start from scratch. But the 3 days to run is
> unacceptible.
>
> Anyone have any approach that they recommend? Also would love to hear
 about
> approaches that do not work!
>
> Thanks
>
> Nick.
>
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 20 2000 - 15:23:18 CST