已经有两个月没有更新博客了，主要实在忙毕设和毕业的一些事情！这两个月也是非常的精彩呀，充分体会到了职场的和校园的不同，作为一名刚毕业就满 1 年工作经验的牛马人，在两个月期间经历了两次调岗、两次降薪，如果再来个裁员那就更精彩了！！哈哈
废话不多说，上菜
今天主要来研究DDL、DML与undo的关系？前两天部门有同事问：
大概意思就是：在执行插入语句时能否理解为它占用的undo表空间和该表所占数据文件的大小一致，我是这么回答的：
小小获得了部门主管的称赞！
在数据库事务四大特性中有一个是原子性，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上，原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。同时，undo log也是MVCC(多版本并发控制)实现的关键。 undo空间主要用于存储事务未提交之前的数据快照，方便回滚事务。理论上在undo里面只需要记录关键信息（最少信息）确保能够回滚到事务之前的数据就可以。所以对于insert语句，undo只需要记录插入行的 ROWID，就可以完成回滚；delete语句需要在 Undo 中记录被删除行所有列的前镜像和其 ROWID； update语句需要在 Undo 中记录被更新列的前镜像和被更新行 ROWID；
究竟为什么呢？对不对呢？
今天就来研究下为什么undo(insert) < undo(update) < undo(delete) < 数据文件中记录的表大小
一、用到的SQL
1、查询当前会话中undo的大小
select vs.name, ms.value
from v$mystat ms, v$sysstat vs
where ms.statistic# = vs.statistic#
and name = 'undo change vector size';ps：后面经常会用到这条SQL，不一一写出了，查看undo大小用这个SQL。表时当前会话产生undo的大小。重进会话可以刷新。2、启用SQL跟踪的SQL，用10046 事件查看跟踪SQL执行情况， 设置level 1只查看基本的 SQL 跟踪，包括 SQL 语句的执行情况。
oradebug setmypid;
oradebug event 10046 trace name context forever,level 1;二、DML语句占undo表空间的大小
结论
先说结论：一般情况下undo(insert) < undo(update) < undo(delete) < 数据文件中记录的表大小
数据操纵语言 Data Manipulation Language DML：SELECT,UPDATE,INSERT,DELETE
实验
1、环境准备：
sqlplus 进入实验环境，查看undo大小为0
NAME VALUE
undo change vector size 0
建表
create table pandatbtb (id int,name varchar2(10));
查undo：
NAME VALUE
undo change vector size 2628
细心的人会发现了，create是DDL语句啊，直接提交不会回滚，为什么会产生undo呢？别急下个实验我们再看。
现在我们需要刷新当前会话的undo：v$mystat和 v$sysstat是 Oracle 数据库中的动态性能视图，分别存储当前会话的统计信息和系统级别的统计信息。 所以退出去，重登录可以刷新会话即刷新当前会话undo，查看undo大小为0就对了。
NAME VALUE
undo change vector size 0
环境准备好了，我们来实验INSERT,DMLUPDATE,DELETE三条语句所占undo的大小吧：
2、insert语句占用undo的大小：
insert into pandatb values (1,'panda01');
查看undo大小：
NAME VALUE
undo change vector size 112
退出重进，或者看增量。
3、update语句占用undo的大小：
update pandatb set name = 'panda02' where id = 1;
查看undo大小：
NAME VALUE
undo change vector size 168
4、delete语句占用undo的大小：
delete panda02 where id = 1;
查看undo大小：
NAME VALUE
undo change vector size 260
由此可见，一般情况下，undo(insert) < undo(update) < undo(delete) 但是由于实验数据过小，不具有普遍性，仅供参考，这不是绝对，在某种情况有可能会发生顺序颠倒的时候。
5、数据文件中记录的表大小
analyze简单收集一下表的统计信息：
SYS@orcl> analyze table PANDATB compute statistics for table;
Table analyzed.
SYS@orcl> select table_name, blocks, empty_blocks, num_rows
from dba_tables
where table_name = 'PANDATB';TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
PANDATB 1 6 1
可以看到现在还有一行数据，用到一个8k的块，还有6个空的块。
扩展1：如何不产生 UNDO 或少量的 UNDO 呢？
在 Oracle 数据库中，DML（数据操作语言）语句通常会生成 undo 数据，以确保事务的原子性、一致性、隔离性和持久性（ACID）。然而，在某些情况下，我们可能希望减少 undo 的生成量，以提高性能或处理特定的需求。以下是一些方法，可以减少 DML 操作生成的 undo 数据量：
1. 使用直接路径插入（Direct-Path Insert）
使用 append 提示进行 insert 叫做直接路径加载插入。加载大数据的时候可以采用这种方式。直接路径插入会绕过缓冲缓存（buffer cache），将数据直接写入数据文件，从而减少 undo 的生成。
INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;
delete /*+ append */ panda02 where id = 1;注意：直接路径插入可能会持久化数据，不可回滚，所以要小心使用。并且加入/+append/提示插入数据后需要马上 commit 事务，不然会出现ERROR 位于第 1 行:ORA-12838: 无法在并行模式下修改之后读/修改对象的错误。 会影响下一次修改失败（insert,update,delete）即使此时 select 这个表都会报错。因此 append 提示的语句首先不能是业务表，其次要尽快提交 commit。
2. 使用批量操作（Bulk Operations）
批量操作可以减少每个单独 DML 语句的开销，从而间接减少 undo 数据量。
使用 PL/SQL 的 FORALL
DECLARE
TYPE numlist IS TABLE OF NUMBER;
mylist numlist := numlist(1, 2, 3, 4, 5);
BEGIN
FORALL i IN mylist.FIRST..mylist.LAST
INSERT INTO my_table (column1) VALUES (mylist(i));
END;3. 减少事务大小
通过减少单个事务中操作的记录数，可以减少每个事务生成的 undo 数据量。
-- 小事务
BEGIN
INSERT INTO my_table VALUES (1, 'data1');
COMMIT;
INSERT INTO my_table VALUES (2, 'data2');
COMMIT;
END;4. 使用分区表（Partitioned Tables）
使用分区表将大表分成多个小的独立部分，可以减少每个分区上的 DML 操作生成的 undo 数据量。
CREATE TABLE partitioned_table (
id NUMBER,
data VARCHAR2(100)
)
PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000)
);5. 使用不可回滚表（Non-Undo Tables）
在某些特殊情况下（如临时表），可以选择使用不产生 undo 数据的表。
CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER,
data VARCHAR2(100)
) ON COMMIT DELETE ROWS;在某些特殊情况下（如临时表），可以选择使用不产生 undo 数据的表。
CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER,
data VARCHAR2(100)
) ON COMMIT DELETE ROWS;6. 调整批量提交频率
在大量 DML 操作中，增加批量提交的频率可以减少每个事务生成的 undo 数据量。
DECLARE
CURSOR c IS SELECT * FROM source_table;
counter INTEGER := 0;
BEGIN
FOR r IN c LOOP
INSERT INTO target_table VALUES (r.id, r.data);
counter := counter + 1;
IF counter = 1000 THEN
COMMIT;
counter := 0;
END IF;
END LOOP;
COMMIT;
END;7. 使用 TRUNCATE 代替 DELETE
TRUNCATE 表操作不会生成 undo 数据，因为它直接重置高水位标记，而不是逐行删除数据。
TRUNCATE TABLE my_table;
注意：TRUNCATE 操作是不可回滚的。
虽然在很多情况下生成 undo 数据是必须的，但通过上述方法，可以有效地减少 DML 操作生成的 undo 数据量，从而提升性能和效率。具体选择哪种方法，需要根据实际业务需求和数据库的使用场景来确定。
扩展2：select会产生undo吗？
select属于DQL是DML的一个分支，查询语句并不会造成数据的更改，不会记录在undo中。但是在某些情况下select也会产生undo1. SELECT FOR UPDATE当使用 SELECT ... FOR UPDATE 语句时，数据库会对被查询的行加锁，以防止其他事务对这些行进行更改。这种情况下，会产生 undo 信息来记录锁的状态，以便在事务回滚时能够正确释放锁。
SELECT * FROM my_table WHERE id = 1 FOR UPDATE;
2. 查询触发了内部修改在某些情况下，查询可能会触发内部的自动修改。例如，某些存储过程或触发器在查询期间被调用，并且这些过程或触发器对数据进行了修改。虽然这种情况不常见，但它确实会导致产生 undo。3. Flashback Query在使用 Flashback Query 功能时，Oracle 需要读取 undo 数据来恢复到指定的时间点。这意味着即使查询本身不产生 undo，它仍然依赖于 undo 数据来完成查询。
SELECT * FROM my_table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
4. 读一致性Oracle 数据库为了实现读一致性，可能会在查询过程中使用 undo 数据来确保返回的数据是一致的，特别是在多事务并发访问的环境中。虽然这不会直接导致查询生成 undo，但 undo 的使用在这个过程中是必要的。5. 索引和统计信息更新有时查询可能会触发统计信息或索引的更新，特别是在查询优化器决定收集统计信息以优化查询性能的情况下。这些更新操作可能会产生 undo。
三、DDL语句占用undo表空间的大小
DDL都是直接提交的？没有回滚这一说，也有会产生undo吗？事实是几乎每个 ddl 操作都会产生 undo,我们来探究一下:ddl 是否会产生 undo:
select vs.name, ms.value
from v$mystat ms, v$sysstat vs
where ms.statistic# = vs.statistic#
and name = 'undo change vector size';NAME VALUE
undo change vector size 0
DDL语句是否占用undo空间探究
SQL> create table pandatb2 as select name from v$datafile;Table created.查看undo大小：
NAME VALUE
undo change vector size 2400
create table 的 ddl 语句产生了大约 2400 bytes 的撤销变化向量
drop table PANDATB2;查看undo大小：
NAME VALUE
undo change vector size 4936
drop table 语句产生 4936-2400=2536 bytes 的 undo 数据，drop多于 create table；从结果中可以看出，DDL 操作也产生 UNDO。
10046跟踪探究DDL过程
有些人可能会奇怪 DDL 怎么产生了 UNDO？DDL 不是不能 ROLLBACK 么？ 我们需要查看 DDL 语句执行过程。这里我们通过 10046 trace 来查看：
猜测：可能是 create table 时 Oracle 需要向基表中 insert 数据，而 drop table 时则需要 delete/update 数据，递归调用了DML语句，比如一些存储过程、在建表的时候需要创建一些基表和隐藏字段，显然后者产生更多的 undo
我们下面用 10046 来跟踪一下 create table 与 drop table 到底做了哪些操作？
1、10046 来跟踪 create table
SYS@orcl> oradebug setmypid;
Statement processed.
SYS@orcl> oradebug event 10046 trace name context forever,level 1;
Statement processed.
SYS@orcl> create table pandatb02 (id int,name varchar2(10));
Table created.
select vs.name, ms.value
from v$mystat ms, v$sysstat vs where ms.statistic# = vs.statistic#
3 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 2312
SYS@orcl>create 产生了 2.3k 的 undo
SYS@orcl> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10078.trc
[root@orcl:/root]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10078.trc | egrep "insert|update|delete"
m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapranges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0,NULL,:17),:18,:19)
insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)values(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
m_stmt:='delete from sdo_geor_ddl__table$$';
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
[root@orcl:/root]$主要是向基表中插入数据可见 DDL 语句递归做了很多 DML 操作，这些都将产生 UNDO。
2、10046 来跟踪 drop table
SYS@orcl> oradebug setmypid;
Statement processed.
SYS@orcl> oradebug event 10046 trace name context forever,level 1;
Statement processed.
SYS@orcl> drop table pandatb02;
Table dropped.
select vs.name, ms.value
from v$mystat ms, v$sysstat vs where ms.statistic# = vs.statistic#
3 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 3012dropped产生了 3k 的 undo
SYS@orcl> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10083.trc
[root@orcl:/root]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10083.trc | egrep "insert|update|delete"
'Need use delete_topo_geometry_layer() to deregister table '
m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
insert into sdo_geor_ddl__table$$ values (2)
select decode(u.type#, 2, u.ext_username, u.name), o.name, t.update$, t.insert$, t.delete$, t.enabled, decode(bitand(t.property, 8192),8192, 1, 0), decode(bitand(t.property, 65536), 65536, 1, 0), decode(bitand(t.property, 131072), 131072, 1, 0), (select o.name from obj$ o where o.obj# = u.spare2 and o.type# =57) from sys.obj$ o, sys.user$ u, sys.trigger$ t, sys.obj$ bo where t.baseobject=bo.obj# and bo.name = :1 and bo.spare3 = :2 and bo.namespace = 1 and t.obj#=o.obj# and o.owner#=u.user# and o.type# = 12 and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj#
delete from object_usage where obj# in (select a.obj# from object_usage a, ind$ b where a.obj# = b.obj# and b.bo# = :1)
delete from sys.cache_stats_1$ where dataobj# = :1
delete com$ where obj#=:1
delete from hist_head$ where obj# = :1
delete from compression$ where obj#=:1
m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
m_stmt:='delete from sdo_geor_ddl__table$$';
delete from sdo_geor_ddl__table$$ where id=2
delete from col$ where obj#=:1
delete from icol$ where bo#=:1
delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)
delete from jijoin$ where obj# in ( select obj# from jijoin$ where tab1obj# = :1 or tab2obj# = :1)
delete from jirefreshsql$ where iobj# in ( select iobj# from jirefreshsql$ where tobj# = :1)
delete from ccol$ where obj#=:1
delete from ind$ where bo#=:1
delete from cdef$ where obj#=:1
delete ecol$ where tabobj# = :1
delete from tab$ where obj#=:1
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete coltype$ where obj#=:1
delete from subcoltype$ where obj#=:1
delete ntab$ where obj#=:1
delete lob$ where obj#=:1
delete refcon$ where obj#=:1
delete from opqtype$ where obj#=:1
delete from cdef$ where obj#=:1
delete from objauth$ where obj#=:1
delete from obj$ where obj# = :1
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
[root@orcl:/root]$3、如果 ddl 操作执行失败又会如何呢？
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
select vs.name, ms.value
from v$mystat ms, v$sysstat vs where ms.statistic# = vs.statistic#
3 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 0
SYS@orcl>
SYS@orcl>
SYS@orcl> oradebug setmypid;
Statement processed.
SYS@orcl> oradebug event 10046 trace name context forever,level 1;
Statement processed.
SYS@orcl> drop table pandatb00;
drop table pandatb00
*
ERROR at line 1:
ORA-00942: table or view does not exist
select vs.name, ms.value
from v$mystat ms, v$sysstat vs where ms.statistic# = vs.statistic#
3 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 112同样产生了 undo,量较少
SYS@orcl> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10223.trc
SYS@orcl>
[root@orcl:/root]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10223.trc | egrep "insert|update|delete"
'Need use delete_topo_geometry_layer() to deregister table '
m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
insert into sdo_geor_ddl__table$$ values (2)执行少量递归操作后，Oracle 发现所要 drop 的对象并不存在，将会 rollback 之前 的"部分"递归 dml 操作其实我们可以把 ddl 操作分解为以下步骤:
begin
commit;
--编译 ddl
begin
--实现 ddl,包括一系列递归的数据字典维护操作及其他操作 commit;
exception
when others then
rollback;
end;
end;ddl 操作无需也不允许手动 commit 或 rollback 参与，但这并不代表 ddl 操作不产生 undo。
参考：
图文结合带你搞定MySQL日志之Undo log(回滚日志)-腾讯云开发者社区-腾讯云
![[机器学习]-人工智能对程序员的深远影响——案例分析](https://i-blog.csdnimg.cn/direct/9dbe86a2baac45dbb42b1dc06e9f7959.png#pic_center)
