Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Undo Generation

RE: Undo Generation

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 28 Jun 2007 15:12:18 -0400
Message-ID: <035301c7b9b8$406a0740$1100a8c0@rsiz.com>


The delete has to store undo information for all the columns in each row. The update only has to store undo information for the column you changed.  

That's the short answer.  


From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of DBA Deepak
Sent: Thursday, June 28, 2007 1:44 PM
To: oracle-l
Subject: Undo Generation  

Hi Gurus,  

Have a simple doubt. I did an experiment to know which DML statement generates the Most/Least amount of UNDO for the same no. of records.  

Steps:


SQL> conn scott/tiger
Connected.

SQL> create table myemp as select * from emp;

Table created.

SQL> delete myemp;

14 rows deleted.

SQL> select used_ublk,used_urec from v$session s,v$transaction t where s.taddr=t.addr and s.username='SCOTT';

 USED_UBLK USED_UREC
---------- ----------

         2 14

SQL> rollback;

Rollback complete.

SQL> update myemp set sal=sal*1.1;

14 rows updated.

 USED_UBLK USED_UREC
---------- ----------

         1 9

SQL> rollback;

Rollback complete.

SQL> insert into myemp select * from emp;

14 rows created.

 USED_UBLK USED_UREC
---------- ----------

         1 1

SQL> rollback;

Rollback complete.


Please note that there was only one session connected to the database as SCOTT user.    

My question is why the do the UNDO generated for DELTE and UPDATE DML statements differ? Because Oracle has to keep undo information of the same no. of records in both the cases.  

Please help me on this.

--

Regards,

Deepak
Oracle DBA

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jun 28 2007 - 14:12:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US