Redo and Undo - Expert Oracle Database Architecture - page 338

the only logged element of the DML operation on a temporary table is the undo, we in fact observed that the DELETE
generated the most undo. The INSERT generated very little undo that needed to be logged. The UPDATE generated
an amount equal to the before image of the data that was changed, and the DELETE generated the entire set of data
written to the undo segment.
As previously mentioned, you must also take into consideration the work performed on an index. You'll find that
an update of an unindexed column not only executes much faster, it also tends to generate significantly less undo
than an update of an indexed column. For example, we'll create a table with two columns, both containing the same
information, and index one of them:
EODA@ORA12CR1> create table t
2 as
3 select object_name unindexed,
4 object_name indexed
5 from all_objects
6 /
Table created.
EODA@ORA12CR1> create index t_idx on t(indexed);
Index created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Now we'll update the table, first updating the unindexed column and then the indexed column. We'll need a new
V$ query to measure the amount of undo we've generated in each case. The following query accomplishes this for
us. It works by getting our session ID ( SID ) from V$MYSTAT , using that to find our record in the V$SESSION view, and
retrieving the transaction address ( TADDR ). It uses the TADDR to pull up our V$TRANSACTION record (if any) and selects
the USED_UBLK column—the number of used undo blocks. Since we currently are not in a transaction, we expect it to
return zero rows right now:
EODA@ORA12CR1> select used_ublk
2 from v$transaction
3 where addr = (select taddr
4 from v$session
5 where sid = (select sid
6 from v$mystat
7 where rownum = 1
8 )
9 )
10 /
no rows selected
But the query will return a row after the UPDATE starts a transaction:
EODA@ORA12CR1> update t set unindexed = lower(unindexed);
72077 rows updated.