Re: Transactions requires much more UNDO space than expected....

> What does the UNDO record contain? The undo record contains change
> vectors, ie information about *what* has changed. Is that the rowid
> plus a 'small overhead' only?

As far as I was aware, all that Oracle needs to maintain when inserting a row is the rowid of the newly inserted row so that it can delete it if the transaction is rolled back. So, yes that is just the ROWID and overhead.

> No, it constitutes the changes at block level.
> Same for indexes, if the index needs to be re-organized internally (ie
> a block splits into two, or a level is added), all of that goes into
> the UNDO.

Fair point.

> So your 'appromixate' calculation is very inaccurate. You would need to
> measure the UNDO generated by an one record transaction, and work from
> there.

So are you saying that I should measure the UNDO generated for the insertion of one row and then multiply that up by 12 Million..?

> And, oh yes, I can imagine this generates 4 Gb undo, especially when
> you should be inserting into empty indexes
>

Can you clarify what you mean by that statement, do you expect more UNDO generated when inserting into an empty index than a partially populated index. If so, why..?

Thanks for your feedback....

Matt Received on Mon Jun 19 2006 - 06:17:04 CDT