oracle中Delete与Truncate语句的对比
一、delete语句
(1)有条件删除
语法格式：delete [from] table_name [where condition];
如：删除users表中的userid为‘001’的数据：delete from users where userid='001';
(2)无条件删除整个表数据
语法格式：delete table_name;
如：删除user表中的所有数据：delete users ;
二、Truncate语句
使用Truncate语句是删除表中的所有记录。
语法格式： Truncate [table] table_name;
(1)删除所有记录不保留记录占用空间
Truncate [table] table_name [drop storage];
如：删除users表中的所有数据并不保存占用空间： Truncate table users drop storage; 由于默认使用drop storage关键字，所以可以省略 drop storage;
(2)删除所有记录保留记录占用空间
Truncate [table] table_name [reuse storage];
如：删除users表中的所有数据并保存占用空间： Truncate table users reuse storage;
三、两种删除语句的对比
由于delete语句删除记录时候，记录是逐条删除的，而Truncate 语句删除数据时不产生回退信息；所以如果需要删除大量数据的时候使用delete则占用较多的系统资源，而如果使用Truncate 则会快的多。
下面通过实例说明一下：
1、首先建立user表：
| 代码如下 | 复制代码 |
|
create table users |
|
2、接着插入一条数据
| 代码如下 | 复制代码 |
|
insert into users values('001','GavinDream','123456'); |
|
3、使用复制插入方法插入几万条数据
| 代码如下 | 复制代码 |
|
insert into users(userid,username,userpass) select * from users; |
|
我插入了 4194304条数据，使用delete删除花费时间为：90.964 seconds，然后又插入了二倍的数据，但使用truncate花费时间仅仅为2.215 seconds，如下图所示：
如果还没搞明白我们再接着看一个测试实例
删除表中的数据的方法有delete,truncate,
它们都是删除表中的数据,而不能删除表结构,delete 可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据,一般我们把delete 操作收作删除表,而truncate操作叫作截断表.
truncate操作与delete操作对比
|
操作
|
回滚
|
高水线
|
空间
|
效率
|
|
Truncate
|
不能
|
下降
|
回收
|
快
|
|
delete
|
可以
|
不变
|
不回收
|
慢
|
下面分别用实例查看它们的不同
1.回滚
首先要明白两点
1.在oracle 中数据删除后还能回滚是因为它把原始数据放到了undo表空间,
2.DML语句使用undo表空间,DDL语句不使用undo,而delete是DML语句,truncate是DDL语句,别外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 deletedSQL> 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(高水线)不变,而truncate表BLOCKS(高水线)变为0
现在我们也看到blocks+empty_blocks=7,也就是oracle分配区时默认一次7+1(表头)=8个blocks;
高水线的作用: 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秒
