DML对undo的影响

INSERT:只生产需要建立日志的很少的UNDO。
UPDATE:生成的UNDO数据量等于修改数据的前映像大小.
DELETE：会生成整个数据集写至UNDO段.

索引会对UNDO产生的量息息相关。

--案例

drop table t1 purge;

create table t1
(
sid int not null primary key,
sname varchar2(10),
tname varchar2(10)
)tablespace test;

 

 

 

declare
        maxrecords constant int:=200000;
        i int :=1;
    begin
        for i in 1..maxrecords loop
          insert into t1 values(i,'ocpyang','ocpyang');
        end loop;
    dbms_output.put_line(' 成功录入数据！ ');
    commit;
    end;
/

 

create index index_01 on t1(sname);

exec dbms_stats.gather_table_stats(user,'T1');

update t1 set tname = upper(tname);

select used_ublk
  from v$transaction
 where addr = (select taddr
                 from v$session
                where sid = (select sid
                               from v$mystat
                              where rownum = 1
                            )
              )
/

---结果为：
 USED_UBLK
----------
      2884

commit;

select used_ublk
  from v$transaction
 where addr = (select taddr
                 from v$session
                where sid = (select sid
                               from v$mystat
                              where rownum = 1
                            )
              )
/

---结果为：
未选定行

update t1 set sname = upper(sname);

select used_ublk
  from v$transaction
 where addr = (select taddr
                 from v$session
                where sid = (select sid
                               from v$mystat
                              where rownum = 1
                            )
              )
/

---结果为：
 USED_UBLK
----------
      6459