Why is rollback so large ?

I deleted 90% of the rows of a table which takes 74 MB of space in dba_segments. This table has 2 indexes, each's size is no more than 30% of the table.

But the rollback size of this transaction is 320 MB. ( select USED_UBLK from v$transaction ). This size also correspond with my observation of the hwmsize in v$rollstat. I made sure no other transaction could use this segment, as it was created fresh and as soon as it is created, I set my transaction to it, and then I immediately alter it offline so no one else could use it.

I have two questions:

1. Does Oracle write index deletion to RBS as well ? I would imagine so.
2. How can the rollback size be 3 times of my deleted segment size including indexes ? My deleted rows are all adjacent within a block, because I am purging based on insertion timestamp.

   I understand Oracle copies the whole block even though there is    only one byte of change. But I believe when you delete the whole    block, it still copy that block once instead of multiple times.

A side note is, there was no other sessions accessing that table during the deletion, so spliting is not an issue.

Thanks for sharing your insight on this. Received on Fri Feb 08 2002 - 22:33:40 CST