sql使用truncate和delete清空table的区别(总结)

概述

1.truncate在各种表上无论是大的还是小的都非常快。如果有rollback命令，delete

将会被撤销，而truncate不会被撤销。

2.truncate是一个ddl语言，向其他所有的dll语言一样，被隐式的提交，不能对truncate

使用rollback命令

3.truncate将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时。进过

truncate操作后的表比delete操作后的表要快的多。

4.truncate不能触发任何delete触发器

5.不能授予任何人清空他人的表的权限

6.当表被清空后，表和表的索引将重新设置成初始大小，而delete不能。

7.不能清空父表。

解释

a.oracle中，delete语句是一种标注动作。在执行过程中，数据库会访问每个符合删除条件的数据行进行标注动作，标记为“已删除”，删除的数据范围越大、执行路径越长，执行sql语句的时间也就越长。所以，delete是一个跟数据规模成正比的执行过程。

b.truncate操作最多接触的知识点是ddl本质。Truncate操作下，数据库并不关注每个数据行和数据范围，而是集中修改端头结构、更新核心数据字典上。对于巨大数据量的表，明显要快于delete。另外，truncate不能删除一个带有外键的表,如果要删除，需要首先取消外键。

Else：truncate操作只产生极少的日志，这些日志不足以rollback，通常认为，truncate操作时不可恢复的，除非使用备份数据进行不完全恢复，或者falshback database。

对于truncate和drop的操作效率：对表毫秒级的truncate没有影响，也就是说大表小表的操作效率来讲是一样的。而对于drop,由于是写日志的，在数据量大的时候回比较慢一点。

创建测试表T(一千万数据量),并进行表分析

查询段的信息

SQL> select extent_id,file_id,block_id,bytes,blocks fromdba_extents where owner='SCOTT' and segment_name='T';

EXTENT_ID    FILE_ID  BLOCK_ID      BYTES     BLOCKS

-------------------- ---------- ---------- ----------

         0          4        176     65536          8

         1          4        184     65536          8

         2          4        192     65536          8

         3          4        200     65536          8

         4          4        208     65536          8

         5          4        216     65536          8

         6          4        224     65536          8

         7          4        232     65536          8

         8          4        240     65536          8

         9          4        248     65536          8

        10          4        256     65536          8

…

EXTENT_ID    FILE_ID  BLOCK_ID      BYTES     BLOCKS

-------------------- ---------- ---------- ----------

       198          4    129792    8388608       1024

       199          4    130816    8388608       1024

       200          4    131840   67108864       8192

       201          4    140032   67108864       8192

       202          4    148224   67108864       8192

 

203 rowsselected.

 

SQL>truncate table t;

 

Tabletruncated.

 

SQL>select extent_id,file_id,block_id,bytes,blocks from dba_extents whereowner='SCOTT' and segment_name='T';

 

 EXTENT_ID   FILE_ID   BLOCK_ID      BYTES    BLOCKS

-------------------- ---------- ---------- ----------

         0         4        176      65536          8

可以看出，truncate之后所有的数据都被清空，数据表和索引段都只保留了一个分区结构。

虽然两个新的段头的extent的大小和起始段都没有发生变化，但是数据字典结构中认为这是一个新的段的结构。

默认的truncate操作，oracle会删除所有数据，回收所有段结构后重新分配一个extent.内部的段结构上，oracle认为是在原来端头的位置上重新分配了一个新段。