整理下delete,drop和truncate的区别

一、相同点：

    truncate和不带where自居的delete，以及drop都会删除表内的数据

二、不同点：

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

    2、delete命令是DML，删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复。

         而truncate，drop命令是DDL，删除的数据是操作立即生效，原数据不放到rollback segment中，不能回滚，数据不可以回滚恢复。

    3、delete命令，不会自动提交事务，操作会触发trigger；而truncate，drop命令，执行后会自动提交事务，操作不触发trigger。

    4、速度：一般来说：drop > truncate > delete

      因为delete语句不影响表所占用的extent，高水位(high watermark)保持原位置不动；而drop语句将表所占用的空间全部释放，truncate语句缺省情况下将空间释放到minextents个extent，除非使用reuse storage；否则truncate会将高水位复位，因为默认情况下truncate table = truncate table drop storage;

PS：使用select语句查询数据时，数据库会扫描高水位线以下的数据块，因为高水位线没有变化，所以扫描的时间不会减少，所以才会出现使用delete删除数据以后，查询的速度还是和delete以前一样。

    以下这个例子操作，就是为了提供删除之后的查询速度：

      问题：有一个拥有1亿条数据的表，只需要保留其中的5条，其他删除，如何做？

      答：这就需要用truncate table来搞定了，如下：

        select 5条数据 into #临时表 from 1亿条数据的牛X表;

        truncate table 1亿条数据的牛X表;   --让它牛X ，不到10毫秒干掉它。

        insert  1亿条数据的牛X表 select * from #临时表;

        drop table #临时表;

    5、安全性：小心使用drop和truncate，尤其没有备份的时候，否则哭都来不及使用上；

        想删除部分数据行用delete，注意带上where子句，回滚段要足够大；

        想删除表，当然用drop；

        想保留表而将所有数据删除，如果和事务无关，用truncate即可；

        如果和事务有关，或者想触发trigger，还是用delete；

        如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

    6、truncate只能对table，delete可以是table，view，synonym；

         truncate table的对象必须是本模式下的，或者有drop any table的权限；

         而delete则是对象必须是本模式下的，或被授予delete on schema.table 或 delete any table的权限。

    7、不能truncate一个带有外键的表，如果要删除首先要取消外键，然后删除；

         truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。

         如果要删除表定义及其数据，请使用drop table语句。对于froeing key约束引用的表，不能使用truncate table，而应使用不带where子句的delete语句。由于truncate table不记录在日志中，所有它不能激活触发器。