Database Reference
In-Depth Information
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
 
 
Search WWH ::




Custom Search