Redo and Undo - Expert Oracle Database Architecture - page 305

It is a common misconception that undo is used to restore the database physically to the way it was before the
statement or transaction executed, but this is not so. The database is logically r estored to the way it was—any changes
are logically undone—but the data structures, the database blocks themselves, may well be different after a rollback.
The reason for this lies in the fact that, in any multiuser system, there will be tens or hundreds or thousands of
concurrent transactions. One of the primary functions of a database is to mediate concurrent access to its data. The
blocks that our transaction modifies are, in general, being modified by many other transactions as well. Therefore, we
can't just put a block back exactly the way it was at the start of our transaction—that could undo someone else's work!
For example, suppose our transaction executed an INSERT statement that caused the allocation of a new extent
(i.e., it caused the table to grow). Our INSERT would cause us to get a new block, format it for use, and put some data
into it. At that point, some other transaction might come along and insert data into this block. If we roll back our
transaction, obviously we can't unformat and unallocate this block. Therefore, when Oracle rolls back, it is really
doing the logical equivalent of the opposite of what we did in the first place. For every INSERT , Oracle will do a DELETE .
For every DELETE , Oracle will do an INSERT . For every UPDATE , Oracle will do an “anti- UPDATE ,” or an UPDATE that puts
the row back the way it was prior to our modification.

■ This undo generation is not true for direct-path operations, which have the ability to bypass undo generation on
the table. We'll discuss these operations in more detail shortly.

Note

How can we see this in action? Perhaps the easiest way is to follow these steps:

1. Create an empty table.

2. Full-scan the table and observe the amount of I/O performed to read it.

3. Fill the table with many rows (no commit).

4. Roll back that work and undo it.

5. Full-scan the table a second time and observe the amount of I/O performed.

So, let's create an empty table:

EODA@ORA12CR1> create table t
2 as
3 select *
4 from all_objects
5 where 1=0;
Table created.

And now we'll query it, with AUTOTRACE enabled in SQL*Plus to measure the I/O.

■ In this example, we will full-scan the table twice each time. The goal is to only measure the I/o performed
the second time in each case. This avoids counting additional I/os performed by the optimizer during any parsing and
optimization that may occur.

Note