删除数据详解！

- 日理万妓 2022-08-12 16:07 158阅读 0赞

**是这样的：**truncate先把原来的表重新命名一下，所以，就像我们之前测试的OBJECT\_ID 是不会变化的。又重新创建了一个表，这个表的名字和被truncate的表的名字相同，但是他的段（也可以说地址）必定发生变化，所以又出现了新的data\_object\_id。这个表是没有内容的，只有这个表的定义。

truncate不会产生大量的roolback，不会占用很多的rollback segments， truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被  立即释放，相当于windows中用shift+delete删除数据，不能够恢复！  
TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比Delete操作后的表要快得多。

**大表在做truncate之前，最好先做下check point，然后按以下步骤操作：**  
1、清空数据并且保留原来的extent  
truncate table table\_name reuse storage;   
注：reuse storage 是truncate的参数，表示保持原来的存储空间，一般我们写的truncate table table\_name 相当于truncate table table\_name drop storage。  
2、逐步回收extent  
ALTER table table\_name DEALLOCATE UNUSED KEEP 1000M;    
ALTER table table\_name DEALLOCATE UNUSED KEEP 800M;    
ALTER table table\_name DEALLOCATE UNUSED KEEP 600M;    
……    
ALTER table subscription\_history\_old DEALLOCATE UNUSED KEEP 10M;   
  
注：执行时，可以根据实际情况调整每次回缩空间的大小。

在执行truncate之前，把trace打开：收集trace文件  
alter session set events = '10046 trace name context forever,level 12';  
执行truncate，然后查看session对应的dump文件，看看你的数据库到底在忙些什么。

**1、TRUNCATE命令**

语法： TRUNCATE TABLE \[schema.\] table \[\{DROP | REUSE\} STORAGE\]  
功能： 删除整个表的数据并释放空间  
由于Truncate 是DDL 命令,所以执行过程中原数据不放在Rollback  Segment 中，不产生回滚数据 ，不产生Redo Log 。 Truncate , Drop 等DDL  
命令都是隐含提交的 。

**例子 :**  
A, B 为两个Table .  
A, B 的数据分别放在 erp\_data 表空间下  
A, B 的索引分别放在 erp\_indx 表空间下  
那么  
我们使用下面的两个语句删除两个表中的数据  
Truncate table A drop storage ;  
Truncate table B reuse storage ;  
得到的结果将是([测试][Link 1]可以参考：[http://www.itpub.net/showthread.php?threadid=588125&pagenumber][http_www.itpub.net_showthread.php_threadid_588125_pagenumber]= )：

Truncate table A drop storage ;  
\--data : 数据部分所在的extent 空间会被释放(释放回收到 minextents个extent)，腾出来的空间可以供其它segment 使用 。

\--index : B(我怎么觉得作者写错了，这地方是A吧？？)表的index 部分会数据删除，extent 部分也被释放，剩下第一个extent .

\--hwm : 会将HWM 重新设置到第一个Block 的位置(hwm 会改变).

Truncate table B reuse storage ;  
\--data : 数据部分所在的extent 空间不会被回收(仅仅数据会被删除)，数据删除之后的freespace 空间只能供本表使用，不可以供其它 segment 使用 。  
\--index : B 表的index 部分会数据删除，但是保留extent 部分  
\--hwm : 会将HWM 重新设置到第一个Block 的位置(hwm 会改变).

**小结：**  
使用Truncate 将会:  
1.清除相应索引(不是Drop,仍然保留extent) ;  
**2.受外键参考的约束(FOREIGN KEY).当然必须先DISABLE 或DROP 相应的外键参考,才能TRUNCATE,注意没有CASCADE 选项 .**  
3.不触发DELETE 触发器  
4.若使用了DROP STORAGE(缺省), 除了已指定的MINEXTENTS,其它EXTENT 重新分配NEXT\_EXTENT 设置MINEXTENTS 之后的EXTENT，High-Water Mark 复位指向表中的第一个BLOCK。 使用REUSE STORAGE 保留表的使用空间 ,保留原来的扩展,但不合并; HWM(高水位)的位置RESET 到第一个BLOCK。 当使DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT 参数。 REUSE  STORAGE 不会缩短表或者调整NEXT 参数。另外一个区别是，用reuse storage可以减少对表及数据字典的锁定时间，特别是大表常这样做，余下和DEALLOCATE  UNUSED 来逐步回收空间。

**Truncate 语句缺省情况(缺省情况为drop storage)下空间释放到 minextents个 extent, 除非使用reuse storage (如果使用reuse storage，那么这些删除之后留下的空间会被保持) ; Truncate 会将高水线复位(回到最开始)。 如果是整理表内部的碎片,可以用truncate 跟上reuse stroage,再重新导入/插入数据。**

**TRUNCATE 一个表时，数据字典内部具体步骤：**

truncate table主要是数据字典的操作..  
检查表上的constraints, 确定是否可以truncate..  
检查mview log的相关信息..  
更新tab$,ind$相关信息(估计与统计信息有关系吧..)  
删除ind\_online$上关于这个表的索引的rebuild online的信息..  
更新seg$表上的相关信息, 清理这个表空间占有的信息..  
更新obj$表上的信息(主要是变更data\_object\_id)

**2、delete和drop**  
Delete 语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动 。 Delete 语句是DML,这个操作会放到rollback segement 中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发。

Drop 语句将表所占用的空间全部释放 。 drop 语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid 状态 。

速度上,一般来说: drop> truncate > delete

**drop table:**  
  
    1)从数据字典删除所有的定义,并清除所有记录.  
  
    2)基于该表的所有的索引、trigger均被删除.  
  
    3)基于该表的所有视图、同义词、PL/SQL程序均不可访问  
  
  4）所有的extent被回收并可重新分配利用。

**truncate、drop 与delete区别**

truncate table命令将快速删除数据表中的任何记录，但保留数据表结构。这种快速删除和delete from数据表的删除全部数据表记录不相同，delete命令删除的数据将存储在系统回滚段中，需要的时候，数据能够回滚恢复，而truncate命令删除的数据是不能够恢复的。

truncate操作同没有where条件的delete操作十分相似  
1、无论truncate大表还是小表速度都非常快。delete要产生回滚信息来满足回滚需求,而truncate是不产生的。  
2、truncate是DDL语句进行隐式提交，不能进行回滚操作  
3、truncate重新设定表和索引的HWM（高水标记），由于全表扫描和索引快速扫描都要读取所有的数据块知道HWM为止,所以全表扫描的性能不会因为delete而提高，但是经过truncate操作后速度会很快  
4、truncate不触发任何delete触发器  
5、不能赋给某个用户truncate其它用户表的权限。如果需要trucate其它用户表的权限必须对该用户赋DROP ANY TABLE权限  
6、当表被truncate后，这个表和索引所占用的空间会恢复到初始大小（都回收了），而delete操作不会减少表或索引所占用的空间。  
7、不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除。

可以做一个测试   
  
建一个带有自增字段的表，加入100万数据   
然后分别用TRUNCATE和DELETE删除全部数据   
然后再向表里插入一条数据   
  
最直观是：   
1.TRUNCATE TABLE是非常快的   
2.TRUNCATE之后的自增字段从头开始计数了，而DELETE的仍保留原来的最大数值   
  
………………………………………………………………………………………………   
注意:这里说的delete是指不带where子句的delete语句   
相同点 ：

truncate和不带where子句的delete, 以及drop都会删除表内的数据   
  
不同点：  
1. truncate和 delete只删除数据不删除表的结构(定义)   
    drop语句将删除表的结构（定义），被依赖的约束(constrain)，触发器(trigger)，索引(index)； 依赖于该表的存储过程/函数将保留，但是变为invalid状态。

2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.   
   truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

3.delete语句不影响表所占用的extent,高水线(highwatermark)保持原位置不动   
  显然drop语句将表所占用的空间全部释放   
  truncate 语句缺省情况下见空间释放到minextents个（一般就是开始的那1个）extent,除非使用reuse storage（使用reuse storage时不是放extents，但是高水位线会降到第一块，没有释放的extens只能供这个表使用，不能让别的表插入数据 ）； truncate会将高水线复位(回到最开始).

4.速度,一般来说: drop> truncate > delete

5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及   
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.   
想删除表,当然用drop   
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.   
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据

6.使用方法：droptable tablename，delete from  tablename，truncatetable tablename   
  
总结：   
  
1、delete和truncate只删除表中数据，而drop删除表  
2、速度上drop>; truncate >; delete  
3、drop和truncate无备份，delete有备份可回滚

**关于oracle使用delete删除的问题**

**问题：**

在oracle里,使用delete删除数据以后，数据库的存储容量不会减少，而且使用delete删除某个表的数据以后，查询这张表的速度和删除之前一样，不会发生变化。

**原因：**

因为oralce有一个HWM高水位，它是oracle的一个表使用空间最高水位线。当插入了数据以后，高水位线就会上涨，但是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。除非使用truncate删除数据。那么，这条高水位线在日常的增删操作中只会上涨，不会下跌，所以数据库容量也只会上升，不会下降。而使用select语句查询数据时，数据库会扫描高水位线以下的数据块，因为高水位线没有变化，所以扫描的时间不会减少，所以才会出现使用delete删除数据以后，查询的速度还是和delete以前一样。

**解决方案：**

1.首先导出表，然后truncate这张表，最后导入这张表。

2.在存储空间当中移动表，但是由于rowid会被打乱，所以需要重建索引.

3.如果是oracle 10g.可是直接更新表的高水位线。

对应的SQL：

9i中：  
create table aa\_bak as select \* from aa where record\_time > sysdate - 10;  
truncate table aa;  
insert into aa select \* from aa\_bak;  
drop table aa\_bak;

10g 版本  
alter tablename enable row movement;  
alter tablename shrink space;

[Link 1]: http://space.itpub.net/35489/viewspace-442302
[http_www.itpub.net_showthread.php_threadid_588125_pagenumber]: http://www.itpub.net/showthread.php?threadid=588125&pagenumber