SQL-基于oracle验证drop、truncate和delete

发表于 更新于 阅读次数：

三者差异

都可以用来删除指定表的所有数据，但三者在删除速度、事务、误删除恢复时的不同，导致在使用上的差异。

delete

1
2
3
4
5
1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中
以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，
可以通过rollback撤消操作。
2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

truncate

1
2
3
4
5
6
1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，
除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作
（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
4、truncatetable不能用于参与了索引视图的表。

drop

1
2
3
1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

总结

1
2
3
4
5
6
7
1、在速度上，一般来说，drop> truncate > delete。
2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；
如果想删除表，当然用drop；
如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
如果和事务有关，或者想触发trigger，还是用delete；
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

验证示例

准备工作

使用scott用户，备份EMP表并启用ROW MOVEMENT

1
2
-- 备份EMP表
CREATE TABLE EMP_BAK AS SELECT * FROM EMP;

drop 表及其表内所有数据

1
2
3
4
5
6
7
8
9
10
11
-- 启用ROW MOVEMENT
ALTER TABLE EMP ENABLE ROW MOVEMENT ;

-- 执行完drop语句后，该表丢失(ddl语句会隐式提交 auto commit)
DROP TABLE EMP;

-- 查询RECYCLEBIN,该drop语句被放在回收站了
SELECT * FROM RECYCLEBIN;

-- 使用FLASHBACK即可将EMP表恢复到drop前状态
FLASHBACK TABLE EMP TO BEFORE DROP;

delete 删除数据(支持事务)

有where条件的delete语句

1
2
3
4
5
6
7
8
9
10
11
-- 1. 查询当前SCN号
select timestamp_to_scn(sysdate) from dual; -- 1278689

-- 2. 执行delete语句并提交
DELETE FROM EMP where EMPNO =7566;
COMMIT;
DELETE FROM EMP where EMPNO =7654;  -- 1278841
COMMIT;

-- 3. 恢复到删除前的状态
FLASHBACK TABLE EMP TO SCN 1278689;

无where条件的delete语句

1
2
3
4
SELECT count(1) FROM EMP; -- 12条 SCN 1279180
DELETE FROM EMP;
COMMIT;                   -- 0条 SCN 1279733
FLASHBACK TABLE EMP TO SCN 1279180;

truncate 删除所有表内数据

1
2
3
4
5
6
7
8
9
10
SELECT count(1) FROM EMP; -- 12条 SCN 1279866
TRUNCATE TABLE EMP;
COMMIT;                   -- 0条 SCN 1279908

--SQL 错误: ORA-01466: unable to read data - table definition has changed
--01466. 00000 -  "unable to read data - table definition has changed"
--*Cause:    Query parsed after tbl (or index) change, and executed
--           w/old snapshot
--*Action:   commit (or rollback) transaction, and re-execute
FLASHBACK TABLE EMP TO SCN 1279866;

oracle flashback相关

  1. 删除table的数据闪回依赖RECYCLEBIN
    使用DROP TABLE XX PURGE无法闪回;
    RECYCLEBIN查询不到(已被清理)的无法闪回;

  2. delete的数据闪回依赖UNDO
    使用TRUNCATE TABLE XX无法闪回

参考

oracle中delete、truncate、drop的区别
Oracle undo表空间管理