Oracle中delete 与 truncate 命令的区别

SQL数据库运维 2022-09-06

2147

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

delete 与 truncate 命令的区别

1. 相同点

从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，二者都能删除表中的数据
一般情况下，主流的关系型数据库都支持这两个命令

使用 Oracle
数据库作为演示

SQL> select * from emp;


      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----------- ---------- --------- ----- ----------- --------- --------- ------
       7369 SMITH      CLERK      7902 1980-12-17     800.00               20
       7499 ALLEN      SALESMAN   7698 1981-02-20    1600.00    300.00     30
       7521 WARD       SALESMAN   7698 1981-02-22    1250.00    500.00     30
       7566 JONES      MANAGER    7839 1981-04-02    2975.00               20
       7654 MARTIN     SALESMAN   7698 1981-09-28    1250.00   1400.00     30
       7698 BLAKE      MANAGER    7839 1981-05-01    2850.00               30
       7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10
       7788 SCOTT      ANALYST    7566 1987-04-19    3000.00               20
       7839 KING       PRESIDENT       1981-11-17    5000.00               10
       7844 TURNER     SALESMAN   7698 1981-09-08    1500.00      0.00     30
       7876 ADAMS      CLERK      7788 1987-05-23    1100.00               20
       7900 JAMES      CLERK      7698 1981-12-03     950.00               30
       7902 FORD       ANALYST    7566 1981-12-03    3000.00               20
       7934 MILLER     CLERK      7782 1982-01-23    1300.00               10


14 rows selected

delete
命令

SQL> delete from emp;


14 rows deleted


SQL> commit;


Commit complete

truncate
命令

SQL>  @d:\oracle.sql;  --重新导入数据


SQL> truncate table emp;


Table truncated

2. 区别

2.1 SQL 性质的不同

delete
：属于 DML(数据操作语言) 范畴
truncate
：属于 DDL(数据定义语言) 范畴

2.2 操作对象不同

delete
：能删除表中或基于真实表创建的视图中的数据
truncate
：只能删除表中的数据，无法应用在视图上

创建测试视图

create or replace view v_emp
as
select * from emp
where sal > 2000;

delete 测试：删除成功

SQL> delete from v_emp;
6 rows deleted


SQL> commit;
Commit complete

truncate 测试：

SQL> truncate table v_emp;
truncate table v_emp


ORA-01702: 视图不适用于此处

2.3 条件限制区别

delete
：删除表中数据时，可以通过 where
关键字，进行选择性删除
truncate
：不能使用 where
关键字进行选择性删除

delete 测试：

SQL> delete from emp where sal > 2000;
6 rows deleted


SQL> commit;
Commit complete

truncate 测试：

SQL> truncate table emp where sal > 2000;
truncate table emp where sal > 2000


ORA-03291: 无效的截断选项 - 缺失 STORAGE 关键字

2.4 事务

delete
：删除表中数据时，涉及到事务处理：回退（缓存）、提交、撤退
truncate
：删除缓存时，不涉及缓存事务处理

delete 测试：

SQL> delete from emp;
14 rows deleted


SQL> select count(1) from emp;
  COUNT(1)
----------
         0

此时，在本图形化工具中，显示 14 条信息已被删除，同时查看员工表，得到 0 条数据
之后，若不执行 commit
命令，通过另一个进程再连接上数据库

此时检索员工表，依旧是 14 条数据，验证了事务不提交后的数据的不完整性（oracle数据库是手动 commit）
再在原窗口执行 commit
命令

事务得到提交，保证了数据的完整性

truncate 测试：

SQL> truncate table emp;
Table truncated


SQL> select count(1) from emp;
  COUNT(1)
----------
         0

若使用 rollback
命令回退：

SQL> rollback;
Rollback complete


SQL> select count(1) from emp;
  COUNT(1)
----------
         0

发现：虽然提示 Rollback complete
，但再次检索数据依旧是 0 。发现 truncate
命令不涉及到事务处理

2.5 删除效率

delete：由于 delete 删除数据时要考虑事务管理（缓存、回退机制、日志记录），所以当删除大批量数据时，速度慢，效率低，甚至达不到删除的目的，delete 是逐行一条一条删除记录的

truncate
：删除大批量数据时，速度快、效率高、但无法撤销，truncate
是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 delete快

测试：

--创建测试表
create table jiker
as select * from emp;


--快速生成数据（表的自动复制）
insert into jiker
select * from jiker;
commit;

最后生成 1400 万条数据 
SQL> 


14680064 rows inserted


Commit complete

使用 delete
命令，已不能在短时间内删除，甚至达不到删除的目的
使用 truncate
命令，可快速删除大批量数据

SQL> truncate table jiker;


Table truncated

2.6 激活触发器

delete：在进行删除操作时，可能激活触发器的处理
truncate：不存在激活触发器处理

delete 测试：

--测试表，复制员工表表结构
create table emp_tmp
as select * 
from emp
where 1 = 2;


--创建触发器
create or replace trigger t_emp
       before delete on emp
       for each row
declare


begin
  --删除数据前出发：将数据备份到临时表 emp_tmp 中
  insert into emp_tmp
  values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno)
end;

--查看测试表中是否有数据
SQL> select * from emp_tmp;


      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----------- ---------- --------- ----- ----------- --------- --------- ------

--删除员工表中的数据
SQL> delete from emp;
14 rows deleted


SQL> select * from emp_tmp;


      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----------- ---------- --------- ----- ----------- --------- --------- ------
       7369 SMITH      CLERK      7902 1980-12-17     800.00               20
       7499 ALLEN      SALESMAN   7698 1981-02-20    1600.00    300.00     30
       7521 WARD       SALESMAN   7698 1981-02-22    1250.00    500.00     30
       7566 JONES      MANAGER    7839 1981-04-02    2975.00               20
       7654 MARTIN     SALESMAN   7698 1981-09-28    1250.00   1400.00     30
       7698 BLAKE      MANAGER    7839 1981-05-01    2850.00               30
       7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10
       7788 SCOTT      ANALYST    7566 1987-04-19    3000.00               20
       7839 KING       PRESIDENT       1981-11-17    5000.00               10
       7844 TURNER     SALESMAN   7698 1981-09-08    1500.00      0.00     30
       7876 ADAMS      CLERK      7788 1987-05-23    1100.00               20
       7900 JAMES      CLERK      7698 1981-12-03     950.00               30
       7902 FORD       ANALYST    7566 1981-12-03    3000.00               20
       7934 MILLER     CLERK      7782 1982-01-23    1300.00               10


14 rows selected

数据复制保存到了 emp_tmp
临时表中，触发器生效

truncate 测试：

SQL> truncate table emp;


Table truncated




SQL> select * from emp_tmp;


      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----------- ---------- --------- ----- ----------- --------- --------- ------

触发器未被触发

2.7 外键约束

delete
：可以运用 SQL
语句、按照业务逻辑、按照一定的先后顺序可以删除相关表中的数据
truncate
：不能删除对于由 foreign key
约束引用的表，不能删除该表中的数据

delete 测试：

直接删除 dept
部门表中的数据

SQL> delete from dept;
delete from dept


ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录

先删除员工表，再删除部门表，成功！

SQL> delete from emp;
14 rows deleted


SQL> commit;
Commit complete


SQL> delete from dept;
4 rows deleted

truncate 测试：

直接删除部门表

SQL> truncate table dept;
truncate table dept


ORA-02266: 表中的唯一/主键被启用的外键引用

先删除员工表，再删除部门表，

SQL> truncate table emp;


Table truncated


SQL> truncate table dept;
truncate table dept


ORA-02266: 表中的唯一/主键被启用的外键引用

依旧不能被删除

3. 应用场景

需要根据一定的业务条件删除数据时、且数据量、性能可控的情况下，可以考虑使用 delete from xxx where 业务条件
需要考虑事务提交、撤销的管理机制时，可以使用 delete
当需要删除大批量数据时，同时要求速度快，效率高并且无需撤销时，可以使用 truncate
另外，在企业级开发中，实际上都是进行逻辑删除（将数据进行删除标识处理）、而并不进行物理上的删除
在实际生产环境中，一般情况下删除业务处理（过渡表）中的数据
在实际企业开发、维护过程中，不管使用 delete
还是 truncate
命令前，都要考虑数据的备份

阿里开发规范
【参考】TRUNCATE　TABLE　比　DELETE 速度快，且使用的系统和事物日志资源少，但TRUNCATE无事务且不触发TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

来源：CSDN

作者：JJJiker

https://blog.csdn.net/JJJikerUPUP/article/details/90677449

点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。

动动小手点击加关注呦☟☟☟

文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle中delete 与 truncate 命令的区别

1. 相同点

2. 区别

2.1 SQL 性质的不同

2.2 操作对象不同

2.3 条件限制区别

2.4 事务

2.5 删除效率

2.6 激活触发器

2.7 外键约束

3. 应用场景

评论