Oracle, how update statement works

Question

Hi,

Question 1

Can anyone tell me if there is any difference between following 2 update statements:

UPDATE TABA SET COL1 = '123', COL2 = '456' WHERE TABA.PK = 1
UPDATE TABA SET COL1 = '123' WHERE TABA.PK = 1

where the original value of COL2 = '456'

how does this affect the UNDO?

Question 2 What about if I update a record in table TABA using ROWTYPE like the following snippet. how's the performance, and how does it affect the UNDO?

SampleRT TABA%rowtype

SELECT * INTO SampleRT FROM TABA WHERE PK = 1;
SampleRT.COL2 = '111';
UPDATE TABA SET ROW = SampleRT WHERE PK = SampleRT.PK;

thanks

Answer 1

For Question 1:

The outcome of the two UPDATEs for rows in your table where PK=1 and COL2='456' is identical. (That is, each such row will have its COL1 value set to '123'.)

Note: there may be rows in your table with PK=1 and COL2 <> '456'. The outcome of the two statements for these rows will be different. Both statements will alter COL1, but only the first will alter the value in COL2, the second will leave it unchanged.

Answer 2

For question 1:

There can be a difference as triggers can fire depending on which columns are updated. Even if you are updating column_a to the same value, the trigger will fire. The UNDO shouldn't be different as, if you expand or shrink the length of a variable length column (eg VARCHAR or NUMBER), all the rest of the bytes of the record need to be shuffled along too.

If the columns don't change size, then you MAY get a benefit in not specifying the column. You can probably test it using v$transaction queries to look at undo generated.

For question 2:

I'd be more concerned about memory (especially if you are bulk collecting SELECT * ) and triggers firing than UNDO. If you don't need SELECT *, specify the columns (eg as follows)

cursor c_1 is select pk, col1, col2 from taba;
SampleRT c_1%rowtype;

SELECT pk, col1, col2 INTO SampleRT FROM TABA WHERE PK = 1;
SampleRT.COL2 = '111';
UPDATE (select pk, col1, col2 from taba) 
SET ROW = SampleRT WHERE PK = SampleRT.PK;

Answer 3

Is your question 1 asking whether UNDO (and REDO) is generated when you're running an UPDATE against a row but not actually changing the value?

Something like?

update taba set col2='456' where col2='456';

If this is the question, then the answer is that even if you're updating a column to the same value then UNDO (and REDO) is generated.

(An exception is when you're updating a NULL column to NULL - this doesn't generate any REDO).