Re: Rollback Segment usage and INSERTs?

Simon,

The way to think of this is that rollback segments grow with a 'before' image of data. If you commit, that data is permanately stored. If you roll back, the 'before' image is read from the rollbck segment and reapplied to memory.

So, when you insert, there is no 'before' image to save, because if you roll back the data is just flushed from memory. With an update or delete, however, rollback grows so that the database has an idea of what the data looked like before modification.

Hope this helps,

Brent Tucker
www.servman.com

Simon Quinn wrote in message ...
>Perhaps I'm missing something but how come Rollback Segment usage is
>very small or not used when inserting into a table using the normal
>INSERT SQL statement.
>
>All I'm doing is doing:
>INSERT INTO target_table
> SELECT * from source_table;
>
>Testing this with 200,000 rows shows that the rollack segment stays
>small, initially configured to be 4MB but does not increase any
>furthur. If I do a DELETE or UPDATE on these tables the Rollback
>Segment becomes large 250Mbytes+ which is what I would expect.
>
>Tested on 8.0.4 and 8.1.5
>
>What am I missing?
>
>Cheers
>Simon Quinn
Received on Fri Sep 24 1999 - 16:17:41 CDT