delete
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
truncate
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
drop
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause
6> TRUNCATE command resets the High Water Mark for the table but DELETE does not. So after TRUNCATE the operations on table are much faster.
If you're removing a large quantity of data (e.g. debug log table) then it makes sense to use truncate to clear the table if you're not worried about needing to retrieve it as part of a transaction - remember that you are stretching your undo tablespace to cover the whole of the data you are looking to delete if you using the DELETE FROM... command, only to immediately wipe that out again when you commit - for what purpose? A lot more processing effort when all you want to do is clear a table of its contents.
On the minus side, if you have a foreign key constraint referring to the table you are trying to truncate, this won't work - even if the referring table has no data in it! This is because the foreign key checking is done with DDL rather than DML. This can be got around by temporarily disabling the foreign key constraint(s) to the table.
With dropping a table, also bear in mind that you lose any associated grants / constraints etc. and if you want to recreate the table, you would need to recreate these - and it may be that the user dropping the table may not have the rights to re-grant etc. - so dropping is always an extreme measure! If you're constantly dropping / recreating you should probably be using temporary tables for one-off jobs or global temporary tables for frequently running processes.
分享到:
相关推荐
sql之truncate_、delete与drop区别.pdf sql之truncate_、delete与drop区别.pdf
truncate,delete以及drop区别汇总,需要可以自己下载看看,个人觉得蛮好的!
sql之truncate、delete与drop区别
详细阐述了Oracle中三种删除的方式truncate,drop和delete三者的区别和联系.
truncate,delete,drop的异同点
作为初学者,有时容易混淆truncate,delete,drop之间的根本区别,所有今日小记一下。。。
delete,truncate和drop的区别
TRUNCATE与 DELETE区别,比较两个的不同的特点
您可能感兴趣的文章:数据库中删除语句Drop、Delete、Truncate的相同点和不同点的比较(实例说明)drop,truncate与delete的区别详解MySQL中DROP,TRUNCATE 和DELETE的区别实现mysql从零开始浅析drop user与delete from ...
主要介绍了秒懂drop、truncate和delete的区别,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
sql中 truncate 和 delete 有什么区别
本文主要讲mysql中三种删除表的操作,delete语句、truncate语句以及drop语句的区别: 简介 delete 1、删除整张表的数据: delete from table_name; 2、删除部分数据,添加where子句: delete from table_name ...
前言 上周同事小姐姐问我:“哈哥你看,我发现...咱们常用的三种删除方式:通过 delete、truncate、drop 关键字进行删除;这三种都可以用来删除数据,但场景不同。 一、从执行速度上来说 drop > truncate >> DELETE 二
注意:这里说的delete是指不带where子句的delete语句 相同点 truncate和不带where子句的delete, 以及drop都会删除表内的数据 不同点: 1. truncate和 delete只删除数据不删除表的结构(定义) drop语句将删除表的结构被...
SQL语句中----删除表数据drop、truncate和delete的用法,对你爱不完
drop、delete、truncate都表示删除,但是三者有一些差别: Delete用来删除表的全部或者一部分数据行,执行delete之后,用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除。会触发这个表上所有的...
9.1.3 drop,delete与truncate的区别
test 博文链接:https://fly533.iteye.com/blog/792601