Hallo Tom,
during christmas break I found time to read
Expert Oracle Database Architecture
in total.
That was great reading, I learnt a lot, thank you !
I have 2 questions on Chapter 9., "Redo and Undo".
The first one is on redo for undo for DML on temporary tables:
a. it is clear that no redo must be generated for that, since temp tables never will be recovered.
b. it is clear that undo must be generated for that, since it might be rollbacked.
c. it is absolutely not clear, why redo for this undo is generated. You just say "redo is written for every undo" hence also for undo for DML on temp tables.
But am I correct, that this redo will never be used, and that it is just a "not yet implemented optimization", that no redo is generated for that at all ?
I mean, probably sometimes in the future there will be implemented a flag for undo "no need to write redo for that", thus allowing
not to write redo for undo for DML on temp tables ?
The second one is on undo, on what I call "undo for trivial updates":
I always falsely thought of undo as physical stuff, always had the concept of "before image" in my head. As you point out, the blocks we modify may also be simultaneously modified by other users, so undo cannot be something "physical", instead of, it has to be something "logical":
The logic of a DML must be able to be undone.
Hence, it is clear, that an insert generates a minimum of undo: Oracle just needs to delete the row in case of rollback, very few undo information is needed for that.
Question is: why does a trivial update ( a column is updated with its value) generate so much undo ?
10gR2 > drop table t;
Table dropped.
10gR2 > create table t as
2 select object_name from all_objects;
Table created.
10gR2 > select used_ublk
2 from v$transaction
3 where addr =
4 (select taddr
5 from v$session
6 where sid = (select sid from v$mystat where rownum=1)
7 );
no rows selected
10gR2 > update t set object_name=lower(object_name);
68791 rows updated.
10gR2 > select used_ublk
2 from v$transaction
3 where addr =
4 (select taddr
5 from v$session
6 where sid = (select sid from v$mystat where rownum=1)
7 );
USED_UBLK
----------
1521
10gR2 > rollback;
Rollback complete.
10gR2 > update t set object_name=object_name;
68791 rows updated.
10gR2 > select used_ublk
2 from v$transaction
3 where addr =
4 (select taddr
5 from v$session
6 where sid = (select sid from v$mystat where rownum=1)
7 );
USED_UBLK
----------
1521
The same amount of undo is generated.
In the first case, Oracle needs to write the before-update-values into the undo.
In the second case, Oracle could choose just to write "no need to undo something" into the undo, instead of that, it seems, it does just the same.
So my suspect is, this also is an optimization "not yet implemented" and probably "might be seen sometimes in the future".
Am I correct ?
Thank you in advance
... c. it is absolutely not clear, why redo for this undo is generated. You just say "redo is written for every undo" hence also for undo for DML on temp tables. ...
it is because undo is protected by redo, we do not differentiate between undo for temporary stuff and undo for permanent stuff. undo is undo.
... But am I correct, that this redo will never be used, and that it is just a "not yet implemented optimization", that no redo is generated for that at all ? ...
it would be used during instance crash recovery and media failure. We need to put undo back the way it was in order to use it. Imagine a crash and we restart and go through the roll forward (recovery) phase - and all of a sudden there are these big blank holes in the undo segments. undo is a complex structure - and the undo for the temporary tables is intermingled with the undo for permanent tables - it is not 'separate'.
As for the last bit about the "set column=column" - yes, that is an optimization we don't have(however, if there is an index on the column... there is an optimization there :) we don't mess with the index unless the value changes.