Oracle truncate table, delete, drop table的区别
关键字: truncate table, delete, 与drop table的区别
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：
二者均删除表中的全部行。
但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。
新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。
如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
---------------------------------
上面是sql联机丛书上说的，似乎很少有书提到truncate table，也似乎很少用到它。今天看到一个问题则正是需要它。
问题：有一个拥有1亿条数据的表，只需要保留其中的5条，其他删除，如何做？
这就需要用truncate table来搞定了，如下：
select 5条数据 into #临时表 from 1亿条数据的牛X表
truncate table 1亿条数据的牛X表
--让它牛X，不到10毫秒干掉它。
insert 1亿条数据的牛X表 select * from #临时表
drop table #临时表
====================================
注意:这里说的delete是指不带where子句的delete语句
相同点:
truncate和不带where子句的delete, 以及drop都会删除表内的数据
不同点:
1. truncate和delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
2.delete语句是DML,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
truncate,drop是DDL, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动。显然drop语句将表所占用的空间全部释放
truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始).
4.速度,一般来说: drop>; truncate >; delete
5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及。
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.
想删除表,当然用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可。 如果和事务有关,或者想触发trigger,还是用delete。
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。
truncate 和delete的区别
一、delete产生rollback，如果删除大数据量的表速度会很慢，同时会占用很多的rollback segments .truncate 是DDL操作，不产生rollback，速度快一些.
Truncate table does not generate rollback information and redo records so it is much faster than delete.
In default, it deallocates all space except the space allocated by MINEXTENTS unless you specify REUSE STORAGE clause.
二、不从tablespace中腾出空间,需要
ALTER TABLESPACE AAA COALESCE; 才有空间
三、truncate 调整high water mark 而delete不.truncate之后，TABLE的HWM退回到 INITIAL和NEXT的位置（默认）
delete 则不可以。
四、truncate 只能对TABLE,不对全表扫描。
delete 可以是table,view,synonym
五、TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限
分享到：
相关推荐
### delete，truncate和drop的区别详解 #### 一、概述 在数据库管理中，经常会遇到需要删除数据或表的情况。为了确保数据的准确性和安全性，理解`delete`、`truncate`和`drop`这三个命令的区别至关重要。本文将详细...
然而，Oracle不支持直接为用户授予针对单个表的`TRUNCATE`权限，而是通常将更广泛的`DROP ANY TABLE`权限赋予用户，但这可能会带来安全隐患。本文将探讨两种实现为用户授权`TRUNCATE TABLE`的方案，以确保安全性和可...
为了应对这类问题，可以通过创建Oracle系统触发器来实现对`DROP TABLE`和`TRUNCATE TABLE`命令的监控与阻止。以下详细介绍两种不同的Oracle版本（10g和8i）下如何创建相应的系统触发器。 ### Oracle 10g环境下的...
Oracle 数据库删除数据 Delete 语句和 Truncate 语句的对比 Delete 语句和 Truncate 语句是 Oracle 数据库中两种常用的删除数据的方法，但是它们之间存在着明显的差异。 一、Delete 语句 Delete 语句是一种 DML...
根据提供的文件信息，以下是从标题、描述以及部分代码示例中提炼出的关于Oracle数据库中与表(Table)操作相关的常用命令及其详细解释： ### 1. 创建表(Create Table) **命令格式**: ``` sql> create table table_...
- `DELETE` 权限通常授予用户对单个记录的删除能力，而 `TRUNCATE TABLE` 需要有更高的权限，比如 `DROP` 权限，因为它涉及到表结构的修改。 - 因此，在设置用户权限时，需要注意区分这两种操作，以防止用户不小心...
在Oracle数据库中，删除表中数据有两种主要方式：Delete语句和Truncate语句。两者在功能上相似，但有着显著的区别，适用于不同的场景。 **Delete语句** Delete语句是一种灵活的数据删除方式，它可以有条件地删除...
根据提供的文件信息，本文将详细解释Oracle数据库中的删除操作，包括DELETE、DROP以及TRUNCATE三个主要命令。这些命令在数据库管理中极为重要，能够帮助我们有效地管理数据表中的记录或整个表结构。 ### Oracle删除...
Oracle Database 10g是Oracle...理解并正确运用`DROP TABLE`、`TRUNCATE TABLE`等命令，结合适当的程序化处理，是数据库管理员必备的技能之一。在实际操作中，务必考虑数据保护和备份，避免因误操作导致的数据丢失。
与`DROP TABLE`和`DELETE FROM`相比，`TRUNCATE TABLE`操作更快，因为它不记录事务日志，也不触发任何触发器。 - **DROP TABLE**：不仅删除所有数据，还会释放表所占用的空间，并且删除与表相关的所有索引、约束和...
总结来说，Oracle 中的 `UPDATE` 语句用于更新表中的数据，而 `DELETE` 和 `TRUNCATE TABLE` 用于删除数据。在执行这些操作时，应始终考虑数据完整性和操作的不可逆性，以确保数据的安全性和准确性。在实际应用中，...
- **TRUNCATE TABLE**：与`DELETE`不同，`TRUNCATE`命令会删除表中的所有数据，但保留表结构。此操作不支持回滚，且速度更快。然而，它不会释放表空间，除非使用`ALTER TABLE ... DEALLOCATE UNUSED`命令。 2. **...
- 示例：`ALTER TABLE employee DROP CONSTRAINT emp_pk;` **6. 禁用约束** - **禁用主键约束：** - `ALTER TABLE 表名 DISABLE PRIMARY KEY;` - 示例：`ALTER TABLE employee DISABLE PRIMARY KEY;` - **禁用...
truncate 方法不同于 delete 方法，它会直接清空表中的所有数据，无法使用闪回方法恢复数据。因此，在使用 truncate 方法时，需要特别小心。 数据库闪回 Oracle 的闪回功能除了以上基本功能外，还可以闪回整个...
这是因为 TRUNCATE TABLE 命令可以释放表空间的占用空间，而 DROP TABLE 和 DELETE 命令不能释放空间。 4. HAVING 子句的用法 HAVING 子句对 GROUP BY 子句所确定的行组进行控制，HAVING 子句条件中只允许涉及常量...
sql> TRUNCATE TABLE table_name; ``` 10. 删除表 使用`DROP TABLE`删除表，`CASCADE CONSTRAINTS`会同时删除依赖于该表的对象： ```sql> sql> DROP TABLE table_name [CASCADE CONSTRAINTS]; ``` 11. 删除列 `...
SELECT 'TRUNCATE TABLE ' || TBNAME || ' DROP STORAGE;' FROM UNIT_TABLES WHERE LX = 'DEL'; ``` 8. **回收未使用空间**：使用ALTER命令回收表和索引未使用的空间。 ```sql SELECT 'ALTER TABLE ' || TBNAME...