Re: Moving a large # of rows from one table to another ?

 Have I already suggested the 'large update' strategy on my website. Break the table up by rowid range (looking at dba_extents), then process each rowid range separately.

Allows multiple concurrent deletes without contention (except in indexes) avoids the problem of increasing processing times due to either

1. deleting in batches by tablescans which have to scan more and more rows each pass, and check read-consistency for more and more rows or
2. deleting in batches through an index with potentially massive single block i/o and further read consistency problems as in (a).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Richard Elliott wrote in message
<01bea6c8$9db04ec0$4b03c896_at_isbarae.transco.twc.com>...
>I have a table that contains many millions of rows. To keep it from growing
>too large I move data off of it to a history table that is not used
>directly and has very few indexes on it. In order to get this to work I had
>to do a very low level move (copy & delete) to avoid rollback segment
>errors. Now that it works it takes a very long time, more time that I have
>in a given window. Is there a utility, or a beter way to do this. It's the
>delete part of the process that demands the huge amount of rollback space
>if not done at a very low level. Once the data has been inserted into the
>backup table I don't care if the delete is rollbackable, can this be turned
>off for a session or a transaction ?
>
Received on Wed May 26 1999 - 14:37:32 CDT