Re: Size of rollback segments for a single transaction

Stephan Schaefer wrote:
>
> John P. Higgins wrote:
>
> The rollback segment must store the values from every column
> that is
> named in the update command. If a table has 10 columns and
> you refer to
> 5 of them in the update command but only 1 column gets a new
> value, the
> rollback must record the before values of the 5 referenced
> columns.
>
> sorry, but I think this is wrong. In the rollback segment you find
> a complete copy of the row with the original values.
> So
> update mytable set column_123 = 1 ;
> means, that you will create a copy of the whole table in your
> rollback segments.
> With an update of one or more columns of one row, you lock the row.
> That means, for any other transaction, this row will be read from
> the rollback segment. It doesn't mean that you get unrefered values
> from original table,referenced columns from rollback segments.
> In the latter case, it should by possible, that one transaction
> could update a column and another transaction could update anoter
> column of the same row.
>
> But in redo log you find
> rownum,old_value,new_value
> only of the changed columns.
>
>
> Stephan Schaefer
> stesch_at_camline.com
>

Well, I wouldn't bet a lot of money, but I still think the before images of only the changed columns are put into the rollback segment. I have seen that the redo log (which records the changes to all segments including rollbacks) has the before images of only the changed columns. To quote the authority, Rama Velpuri, "For update transactions, we store the old value of the updated columns."

Part of my original point has to do with which columns are in the rollback. If my update statement specifies columns which actually did not change, Oracle still puts them in the rollback and the redo log.

We have a purchased system where the programmers routinely "update" all the columns even though only one or two changed. This is one way to achieve 4GB/hour in logging!

On the other point, locking is not the issue. If another transaction updates and commits (unlocks) a row after I started and before I get to that row, I have to read from the rollback to keep my consistent read. Received on Fri Mar 13 1998 - 00:00:00 CST