truncate、drop、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,再重新导入/插入数据

第二部分

1。delete from后面可以写条件，truncate不可以

2。delete from记录是一条条删的，所删除的每行记录都会进日志，而truncate一次性删掉整个页，因此日至里面只记录页释放，简言之，delete from更新日志，truncate基本不，所用的事务日志空间较少

3。delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页

4。当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。

5。如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变，而truncate后，种子会恢复初始

6。truncate不会触发delete的触发器，因为truncate操作不记录各个行删除

第三部分

1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。

2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。

3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。

4、TRUNCATE不能触发任何DELETE触发器。

5、不能授予任何人清空他人的表的权限。

6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。7、不能清空父表。

总结

TRUNCATE TABLE：删除内容、释放空间但不删除定义。

DELETE TABLE:删除内容不删除定义，不释放空间。

DROP TABLE：删除内容和定义，释放空间。

扩展

SQL语言包括三种主要程序设计语言类别的语句：数据定义语言(DDL)，数据操作语言(DML)(插，删，改，排，检)及数据控制语言(DCL)。

常见的DDL语句

CREATE DATABASE

　　创建数据库

　　CREATE {DATABASE | SCHEMA} db_name

　　[create_specification [, create_specification] ...]

　　create_specification:

　　[DEFAULT] CHARACTER SET charset_name

　　| [DEFAULT] COLLATE collation_name

　　CREATE TABLE

　　创建数据库表格

　　CREATE [TEMPORARY] TABLE tbl_name

　　[(create_definition,...)]

　　[table_options] [select_statement]

　　 ALTER TABLE

　　修改数据库表格

　　ALTER TABLE tbl_name

　　alter_specification [, alter_specification] ...

　　alter_specification:

　　ADD [COLUMN] column_definition [FIRST | AFTER col_name ]

　　| ADD [COLUMN] (column_definition,...)

　　| ADD INDEX (index_col_name,...)

　　| ADD [CONSTRAINT [symbol]]

　　PRIMARY KEY (index_col_name,...)

　　| ADD [CONSTRAINT [symbol]]

　　UNIQUE (index_col_name,...)

　　| ADD (index_col_name,...)

　　| ADD [CONSTRAINT [symbol]]

　　FOREIGN KEY (index_col_name,...)

　　[reference_definition]

　　| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

　　| CHANGE [COLUMN] old_col_name column_definition

　　[FIRST|AFTER col_name]

　　| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

　　| DROP [COLUMN] col_name

　　| DROP PRIMARY KEY

　　| DROP INDEX index_name

　　| DROP FOREIGN KEY fk_symbol

　　| DISABLE KEYS

　　| ENABLE KEYS

　　| RENAME [TO] new_tbl_name

　　| ORDER BY col_name

　　| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

　　| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

　　| DISCARD TABLESPACE

　　| IMPORT TABLESPACE

　　| table_options

　　 DROP TABLE

　　删除数据库表格

　　DROP [TEMPORARY] TABLE

　　tbl_name [, tbl_name] ...

　　[RESTRICT | CASCADE]

　　CREATE VIEW

　　创建查询命令

　　CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

　　VIEW view_name [(column_list)]

　　AS select_statement

　　[WITH [CASCADED | LOCAL] CHECK OPTION]

　　 ALTER VIEW

　　修改查询命令

　　ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

　　VIEW view_name [(column_list)]

　　AS select_statement

　　[WITH [CASCADED | LOCAL] CHECK OPTION]

　　 DROP VIEW

　　删除查询命令

　　DROP VIEW

　　view_name [, view_name] ...

　　[RESTRICT | CASCADE]

　　 TRUNCATE TABLE

　　删除数据表内容

　　TRUNCATE TABLE name [DROP/REUSE STORAGE]　

　　DROP STORAGE：显式指明释放数据表和索引的空间　

　　REUSE STORAGE：显式指明不释放数据表和索引的空间