文档章节

TRUNCATE 与 DELETE 的区别

xsilen
 xsilen
发布于 2014/08/25 16:25
字数 1309
阅读 17
收藏 0
点赞 0
评论 3

实例对比Oracle中truncate和delete的区别

删除表中的数据的方法有delete,truncate, 它们都是删除表中的数据,而不能删除表结构,delete 可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,truncate只能删除整个表的数据,一般我们把delete 操作收作删除表,truncate操作叫作截断表. truncate操作与delete操作对比  

操作 回滚 高水线 空间 效率
Truncate 不能 下降 回收
delete 可以 不变 不回收

  下面分别用实例查看它们的不同

1.回滚

首先要明白两点 1.oracle 中数据删除后还能回滚是因为它把原始数据放到了undo表空间, 2.DML语句使用undo表空间,DDL语句不使用undo,deleteDML语句,truncateDDL语句,别外DDL语句是隐式提交. 所以truncate操用不能回滚,delete操作可以. 两种操作对比(首先新建一个表,并插入数据)  

SQL> create table t   2  (   3  i number   4  ); Table created. SQL> insert into t values(10); SQL> commit; Commit complete. SQL> select * from t;          I ----------         10

Delete删除,然后回滚  

SQL> delete from t; 1 row deleted. SQL> select * from t; no rows selected #后回 SQL> rollback; Rollback complete. SQL> select * from t;          I ----------         10

Truncate截断表,然后回滚.  

SQL> truncate table t; Table truncated. SQL> rollback; Rollback complete. SQL> select * from t; no rows selected

可见delete删除表还可以回滚,truncate截断表就不能回滚了.(前提是delete操作没有提交)

2.高水线

所有的Oracle表都有一个容纳数据的上限(很象一个水库历史最高的水位),我们把这个上限称为“high water mark”HWM。这个HWM是一个标记(专门有一个数据块用来记录高水标记等),用来说明已经有多少数据块分配给这个表. HWM通常增长的幅度为一次5个数据块. delete语句不影响表所占用的数据块高水线(high watermark)保持原位置不动 truncate 语句缺省情况下空间释放,除非使用reuse storage;   truncate会将高水线复位 下面对两种操作对比  

SQL> analyze table t estimate statistics; Table analyzed. SQL> select segment_name,blocks from dba_segments where segment_name=upper('t'); SEGMENT_NAME                       BLOCKS ------------------------------ ---------- T                                      24 SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t'); TABLE_NAME                         BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ T                                      20            3

USER_TABLES.BLOCKS 列代表该表中曾经使用过得数据库块的数目,即水线。 注意:USER_TABLES.BLOCKS EMPTY_BLOCKS (20+3=23)DBA_SEGMENTS.BLOCKS少一个数据库块,这是因为有一个数据库块被保留用作表头。DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。USER_TABLES.BLOCKS表示已经使用过的数据库块的数目(水线) Delete删除表,  

SQL> delete from t; 10000 rows deleted SQL> commit; Commit complete. SQL> analyze table t estimate statistics; Table analyzed. SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t'); TABLE_NAME                         BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ---------------------------------------------------------------- T                                      20            3

Truncate截断表  

SQL> truncate table t; Table truncated. SQL> analyze table t estimate statistics; Table analyzed. SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t'); TABLE_NAME                         BLOCKS EMPTY_BLOCKS ------------------------------ ---------- -------------------------------------------------------- T                                       0            7

可见,delete,BLOCK(高水线)不变,truncateBLOCKS(高水线)变为0 现在我们也看到blocks+empty_blocks=7,也就是oracle分配区时默认一次7+1(表头)=8blocks; 高水线的作用: HWM数据库的操作有如下影响: a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块,即使该表中没有任何数据。 b) 即使HWM以下有空闲的数据库块,键入在插入数据时使用了append关键字,则在插入时使用HWM以上的数据块,此时HWM会自动增大。 因此高水线是oracle优化时一个重要的参数

3.空间

既然高水线用来说明已经有多少数据块分配给这个表,那么高水线也可理解为表的空间占用。 即使delete将表中的数据全部删除,HWM还是为原值,所以还有那么多的空间分配给这个表,即它的空间还没有回收, truncate表后高水线变为0,那现在它就表示没有分配空间,即它的空间被回收了。

4.效率

要想查看delete,truncate那个效率更高,先构建一个大表,然后查看它们分别对些表删除所需的时间。 有个相当形象的比喻:领导给你两本书让你扔掉,delete就是你守在复印机前,把书一页页撕下来复印一份,再一页页扔到垃圾桶里,truncate就是直接把两本书扔到垃圾桶里,那个快那个慢不言而喻。 先在表中插入100000条记录,并打开时间  

SQL> set timing on; SQL> begin   2  for i in 1..100000 loop   3  insert into t values('10');   4  commit;   5  end loop;   6  end;   7  / PL/SQL procedure successfully completed. Elapsed: 00:01:12.50

Delete删除表  

SQL> delete from t; 100000 rows deleted. Elapsed: 00:00:20.09

Truncate 截断表  

#先把表回滚 SQL> rollback; Rollback complete. Elapsed: 00:00:17.36 SQL> select count(*) from t;   COUNT(*) -------------------     100000 Elapsed: 00:00:00.01 SQL> truncate table t; Table truncated. Elapsed: 00:00:00.20

可见删除同一个大小的表,delete用了20.09秒,而truncate只用了0.2.


本文转载自:http://chenxy.blog.51cto.com/729966/168459

共有 人打赏支持
xsilen
粉丝 1
博文 5
码字总数 8773
作品 0
闸北
高级程序员
加载中

评论(3)

xsilen
xsilen

删除表中的数据的方法有delete,truncate,
它们都是删除表中的数据,而不能删除表结构,delete 可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据,一般我们把delete 操作叫作删除表,而truncate操作叫作截断表.
truncate 操作与 delete 操作对比
truncate 操作与 delete 操作对比
操作    回滚    高水线    空间    效率
Truncate    不能    下降    回收   快
delete    可以    不变    不回收   慢

TRUNCATE和DELETE有以下几点区别
1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销,而TRUNCATE则不会被撤销。
2、TRUNCATE是一个DDL语言,向其他所有的DDL语言一样,他将被隐式提交,不能对TRUNCATE使用ROLLBACK命令。
3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时,经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
4、TRUNCATE不能触发任何DELETE触发器。
5、不能授予任何人清空他人的表的权限。
6、当表被清空后表和表的索引将重新设置成初始大小,而delete则不能。
7、不能清空父表。
xsilen
xsilen
Mysql 里的 DELETE 与 TRUNCATE,DELETE可以加条件,排序,限制行数,而TRUNCATE是整表删除
DELETE 与 TRUNCATE 有什么不同?

删除表中的数据的方法有delete,truncate, 它们都是删除表中的数据,而不能删除表结构,delete 可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据,...

zh119893
2013/05/24
192
1
truncate delete drop 区别

truncate和 delete只删除数据不删除表的结构(定义); drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态。...

Junn
2012/10/05
0
0
truncate 、delete与drop区别

相同点: 1.truncate和不带where子句的delete、以及drop都会删除表内的数据。 2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。 不同点: TRUNCATE TABLE:删除内容、释放空间但...

wersdffg
2015/10/21
98
1
drop,delete与truncate的区别

drop直接删掉表; truncate删除的是表中的数据,再插入数据时自增长的数据id又重新从1开始; delete删除表中数据,可以在后面添加where字句。 (1) DELETE语句执行删除操作的过程是每次从表...

博为峰教研组
2016/12/13
3
0
详解Oracle DELETE和TRUNCATE 的区别

语法 delete from aa truncate table aa 区别 1.delete from后面可以写条件,truncate不可以。 2.delete from记录是一条条删的,所删除的每行记录都会进日志,而truncate一次性删掉整个页,因...

yzbty23
2016/06/29
18
0
SQL truncate 、delete与drop区别

SQL truncate 、delete与drop区别 相同点: 1.truncate和不带where子句的delete、以及drop都会删除表内的数据。 2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。 不同点: 1....

yh_pub
2016/11/08
0
0
Truncate Table-笔记

1、语法: TRUNCATE TABLE name 2、参数释义 name是要截断的表的名称或要删除其全部行的表的名称。 3、区别 1)、TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表...

hiqj
2015/04/08
0
0
truncate,delete与drop的区别

truncate table命令将快速删除数据表中的所有记录,但保留数据表结构。这种快速删除与delete from 数据表的删除全部数据表记录不一样,delete命令删除的数据将存储在系统回滚段中,需要的时候...

五大三粗
2015/07/10
0
0
truncate和delete区别

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。 但 TRUNCATE TABLE 比 DELETE 速度快(删除表空间,不记录,所以不支持回滚),且使用的系统和事务日...

chape
2013/09/04
0
0
Mysql清空表(truncate)与删除表中数据(delete)的区别

为某基于wordpress搭建的博客长久未除草,某天升级的时候发现已经被插入了几万条垃圾留言,如果一条条删除那可真是累人的活。遂考虑直接进入mysql直接清空表或者删除表中数据。 本文记录一下...

三奇
01/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

代码生成利器:IDEA 强大的 Live Templates

代码生成利器:IDEA 强大的 Live Templates

qwfys
17分钟前
1
0
spring boot使用通用mapper(tk.mapper) ,id自增和回显等问题

最近项目使用到tk.mapper设置id自增,数据库是mysql。在使用通用mapper主键生成过程中有一些问题,在总结一下。 1、UUID生成方式-字符串主键 在主键上增加注解 @Id @GeneratedValue...

北岩
21分钟前
0
0
告警系统邮件引擎、运行告警系统

告警系统邮件引擎 cd mail vim mail.py #!/usr/bin/env python#-*- coding: UTF-8 -*-import os,sysreload(sys)sys.setdefaultencoding('utf8')import getoptimport smtplibfr......

Zhouliang6
24分钟前
0
0
日常运维--rsync同步工具

rsync命令是一个远程数据同步工具,可通过LAN/WAN快速同步多台主机间的文件。rsync使用所谓的“rsync算法”来使本地和远程两个主机之间的文件达到同步,这个算法只传送两个文件的不同部分,而...

chencheng-linux
28分钟前
1
0
Java工具类—随机数

Java中常用的生成随机数有Math.random()方法及java.util.Random类.但他们生成的随机数都是伪随机的. Math.radom()方法 在jdk1.8的Math类中可以看到,Math.random()方法实际上就是调用Random类...

PrivateO2
40分钟前
1
0
关于java内存模型、并发编程的好文

Java并发编程:volatile关键字解析    volatile这个关键字可能很多朋友都听说过,或许也都用过。在Java 5之前,它是一个备受争议的关键字,因为在程序中使用它往往会导致出人意料的结果。在...

DannyCoder
昨天
0
0
dubbo @Reference retries 重试次数 一个坑

在代码一中设置 成retries=0,也就是调用超时不用重试,结果DEBUG的时候总是重试,不是0吗,0就不用重试啊。为什么还是调用了多次呢? 结果在网上看到 这篇文章才明白 https://www.cnblogs....

奋斗的小牛
昨天
0
0
数据结构与算法3

要抓紧喽~~~~~~~放羊的孩纸回来喽 LowArray类和LowArrayApp类 程序将一个普通的Java数组封装在LowArray类中。类中的数组隐藏了起来,它是私有的,所以只有类自己的方法才能访问他。 LowArray...

沉迷于编程的小菜菜
昨天
0
0
spring boot应用测试框架介绍

一、spring boot应用测试存在的问题 官方提供的测试框架spring-boot-test-starter,虽然提供了很多功能(junit、spring test、assertj、hamcrest、mockito、jsonassert、jsonpath),但是在数...

yangjianzhou
昨天
0
0
rsync工具介绍/rsync通过ssh同步

rsync工具介绍 数据备份是必不可少,在Linux系统下数据备份的工具很多,其中重点介绍就是rsync工具,rsync不仅可以远程同步数据,还可以本地同步数据,且不会覆盖以前的数据在已经存在的数据...

Hi_Yolks
昨天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部