truncate drop delete difference
来源:互联网 发布:中关村人工智能活动季 编辑:程序博客网 时间:2024/04/26 12:27
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.
SQL> SELECT COUNT(*) FROM emp; COUNT(*)---------- 14SQL> DELETE FROM emp WHERE job = 'CLERK';4 rows deleted.SQL> COMMIT;Commit complete.SQL> SELECT COUNT(*) FROM emp; COUNT(*)---------- 10
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.
SQL> TRUNCATE TABLE emp;Table truncated.SQL> SELECT COUNT(*) FROM emp; COUNT(*)---------- 0
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.
SQL> DROP TABLE emp;Table dropped.SQL> SELECT * FROM emp;SELECT * FROM emp *ERROR at line 1:ORA-00942: table or view does not exist
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
From Oracle 10g a table can be "undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP;Flashback complete.
---------------------------------------------------------------------
Difference
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.
(A Rollback Segment is a database object containing before-images of data written to the database.
Rollback segments are used to:
- Undo changes when a transaction is rolled back
- Ensure other transactions do not see uncommitted changes made to the database
- Recover the database to a consistent state in case of failures
- truncate drop delete difference
- Difference between TRUNCATE, DELETE and DROP commands
- Difference between TRUNCATE, DELETE and DROP commands
- truncate,delete,drop
- delete,truncate,drop 区别
- truncate,drop,delete區別
- truncate,delete,drop 比较
- drop truncate delete区别
- truncate delete drop 区别
- Oracle truncate,delete,drop
- truncate,drop,delete 比较
- truncate,delete,drop
- SQL drop,delete,truncate
- drop, truncate, delete
- truncate、delete、drop区别
- drop、truncate、delete
- drop delete truncate 区别
- TRUNCATE,DELETE,DROP区别
- Android中使单选项列表框默认选中前次保存的值(即setSingleChoiceItems方法的使用)
- C++学习笔记之文件和流
- WinCE6.0 Camera驱动源码分析(二)
- executeQuery()、executeUpdate() 和 execute()
- Unity3D Android游戏添加google广告的方法——使用AdMob
- truncate drop delete difference
- 正则表达式学习(二)正则表达式的操作符及说明
- JSP九大内置对象的作用和用法总结?
- Andriod OpenGL 教程 03 - 添加颜色
- hdu 1044Collect More Jewels ( bfs + dfs )
- STL中的排序算法一览
- [java]Math中的ceil--floor--round
- MPEG-4 AVC/H.264 一些知识
- qt 编译c文件,extern “c”