delete、drop和truncate

相同点

truncate和不带where的delete以及drop都会删除表内的所有数据

不同点

• 删除的内容
  • truncate和delete只删除数据不删除表的结构（定义）
  • drop语句将删除表的结构，即被依赖的约束（constrain）、触发器（trigger）、索引（index）

依赖于该表的存储过程/函数将保留，但是变为invalid状态

• 数据库语言的类型

  • delete是DML，删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复
  • truncate、drop是DDL，删除的数据是操作立即生效，原数据不放到rollback segment中，数据不可以回滚恢复

• 事务的提交

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

• 执行速度

  • 一般来说：drop > truncate > delete
  • 因为delete语句不影响表所占用的extent，高水位（high watermark）保持原位置不动；
  • drop语句将表所占用的空间全部释放
  • truncate语句缺省情况下将空间释放到minextents个extent，除非使用reuse storage

否则truncate会将高水位复位，因为默认情况下truncate table = truncate table drop storage;

使用select语句查询数据时，数据库会扫描高水位线以下的数据块，因为高水位线没有变化，所以扫描的时间不会减少，所以才会出现使用delete删除数据以后，查询的速度还是和delete以前一样
问题：有一个拥有1亿条数据的表a，只需要保留其中的5条，其他删除，如何做？ 答：这就需要用truncate table来搞定了

SELECT 5条数据
INTO 临时表
FROM 表a;
TRUNCATE TABLE 表a;
INSERT INTO 表a
SELECT *
FROM 临时表;
DROP TABLE 临时表;

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