This is a frequently asked but easily answered question. The presence of indexes (or the fact that a table is an index-organized table) may affect the amount of undo generated dramatically, as indexes are complex data structures and may generate copious amounts of undo information.

That said, an INSERT will, in general, generate the least amount of undo, since all Oracle needs to record for this is a rowid to “delete.” An UPDATE is typically second in the race (in most cases). All that needs to be recorded are the changed bytes. It is most common that you UPDATE some small fraction of the entire row’s data.

Therefore, a small fraction of the row must be remembered in the undo. Many of the previous examples run counter to this rule of thumb, but that’s because they update large, fixed-size rows and they update the entire row. It is much more common to UPDATE a row and change a small percentage of the total row. A DELETE will, in general, generate the most undo. For a DELETE, Oracle must record the entire row’s before image into the undo segment.

The previous temporary table example, with regard to redo generation, demonstrated that fact: the DELETE generated the most redo, and since 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:

$ sqlplus eoda/foo@PDB1
SQL> create table tasselect object_name unindexed,object_name indexedfrom all_objects;Table created.
SQL> create index t_idx on t(indexed); Index created.
SQL> exec dbms_stats.gather_table_stats(user,’T’); PL/SQL procedure successfully completed.

Note You will not see the exact same results when you run these tests on your database. It will depend on the number of objects in your database, which will be different from database to database.

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’vegenerated 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:

SQL> select used_ublk from v$transaction where addr = (select taddr from v$session where sid = (select sid from v$mystat where rownum = 1));no rows selected

But the query will return a row after the UPDATE starts a transaction:

SQL> update t set unindexed = lower(unindexed); 72522 rows updated.
SQL> select used_ublkfrom v$transaction where addr = (select taddr from v$session where sid = (select sid from v$mystat where rownum = 1));USED_UBLK 1369
SQL> commit;Commit complete.

That UPDATE used 1369 blocks to store its undo. The commit would free that up, or release it, so if we rerun the query against V$TRANSACTION, it would once again show us no rows selected. When we update the same data—only indexed this time—we’ll observe the following:

SQL> update t set indexed = lower(indexed); 72522 rows updated.
SQL> select used_ublk from v$transaction where addr = (select taddr from v$session where sid = (select sidfrom v$mystatwhere rownum = 1));USED_UBLK 3154

As you can see, updating that indexed column in this example generated several times as much undo. This is due to the inherit complexity of the index structure itself and the fact that we updated every single row in the table—moving every single index key value in this structure.