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

On Tue, 25 May 1999 16:09:19 GMT, "Richard Elliott" <Richard.A.Elliott_at_WGP.TWC.COM> wrote:

>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 ?
>

I had a similar problem with deleting millions of rows and leaving some behind. What I did was:

Delete from mytable where blah,blah,blah... and rownum < 500000; Commit;

I repeated the delete as many times as required. The value of rownum can be adjusted to fit in your available rollback. I don't know if this is very fast, but it at least keeps the rollback requirements to a managable size. Received on Thu May 27 1999 - 01:34:02 CDT