For updates that effect multiple rows, Oracle does the following for each row: Read the data block, or locate it in cache; Lock the buffer; Prepare a change vectors to update the row; Copy the change vector into the redo log buffer; Apply the change vector. This is of course a simplification, because there will normally be rollback segment changes to be made as well, and there may be index changes as well. However, the point to note is that each row is processed independently.

A discrete transaction cannot process two rows in the same block, because it does not apply its changes until it commits, and therefore when considering a second change to the block it would not have a before image of the block available. There are other reasons why it would not work, but this is the most intuitively obvious.

I have a script which does a table update, basically it works on 2 tables (around 40000 rows each), update some records on one table if certain conditions are met. It takes around 1.5 hr on a SUN E450.

I was trying to use discrete transaction to reduce the undo segment data, to increase speed. Since I can not guarantee during the update it will not update ONE DATA BLOCK ONLY ONCE (as required by ORACLE discrete transaction), I got "dead lock detected" error. My question is why oracle put this limit, is there a way to force oracle not generate UNDO data even if I am updating a table and confident nobody is going to use that table and do not care if the transaction aborted because some unforeseen reason.