Difference between TRUNCATE, DELETE and DROP commands
来源:互联网 发布:搜狗输入法mac设置 编辑:程序博客网 时间:2024/04/24 05:48
The DELETE command is used to remove the Tables data either selective or all and put the data in an rollback segment as well where from a read consistent view or a flashback view can be seen even after commit and one can rollback the tables data after delete as well. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.one more thing delete doesn' resets the HWM and space occupied with insert can't be reclaim by delete.when you deletes table data indexes are not dropped.Its a DML statement。We can use undo space。
Truncate command removes all the data from table and drops all integrity constraints it's an DDL Statement and resets the HWM but you can't rollback a tables data after truncate, As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.We can not use undo space and one more thing truncate doesn' remove selective rows it can only removes the full tables data.truncate leaves the database structure ad is a good tool to reset HWM.but a dangerous tool for database security.
hr@ORCL> select * from t; ID TEST---------- ---------- 1 g 3 d 5 thr@ORCL> commit;Commit complete.hr@ORCL> delete from t where id=5;1 row deleted.hr@ORCL> select * from t; ID TEST---------- ---------- 1 g 3 dhr@ORCL> rollback;Rollback complete.hr@ORCL> select * from t; ID TEST---------- ---------- 1 g 3 d 5 t
Truncate command removes all the data from table and drops all integrity constraints it's an DDL Statement and resets the HWM but you can't rollback a tables data after truncate, As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.We can not use undo space and one more thing truncate doesn' remove selective rows it can only removes the full tables data.truncate leaves the database structure ad is a good tool to reset HWM.but a dangerous tool for database security.
hr@ORCL> select * from t; ID TEST---------- ---------- 1 g 3 d 5 thr@ORCL> truncate table t;Table truncated.hr@ORCL> select * from t;no rows selectedhr@ORCL> rollback;Rollback complete.hr@ORCL> select * from t;no rows selected
The drop command means delete the table from the database. The drop command is a data definition language (DDL) command and this is a table oriented command. We can delete all rows and also the table definition, indexes, privileges,triggers and all storage parameters. The rollback is not possible. We can not use undo space。
hr@ORCL> drop table t;Table dropped.hr@ORCL> select * from t;select * from t *ERROR at line 1:ORA-00942: table or view does not existhr@ORCL> rollback;Rollback complete.hr@ORCL> select * from t;select * from t *ERROR at line 1:ORA-00942: table or view does not exist
In sum,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:
hr@ORCL> flashback table t to before drop;Flashback complete.
- Difference between TRUNCATE, DELETE and DROP commands
- Difference between TRUNCATE, DELETE and DROP commands
- truncate drop delete difference
- Oracle-The Main Difference Between DELETE & TRUNCATE
- Difference between Truncate and Delete command in SQL - Interview Questions with Example
- Difference between new (delete) and malloc (free)
- What are the difference between DDL, DML and DCL commands?
- What are the difference between DDL, DML and DCL commands?
- What are the difference between DDL, DML and DCL commands?
- What are the difference between DDL, DML and DCL commands?
- What are the difference between DDL, DML and DCL commands?
- what is the difference between "delete []m_data"and "delete m_data"
- truncate,delete,drop
- delete,truncate,drop 区别
- truncate,drop,delete區別
- truncate,delete,drop 比较
- drop truncate delete区别
- truncate delete drop 区别
- 黑客与票贩子联手入侵185个政府网站
- 蛋疼的getJSON和IE
- ARM7启动代码的分析与设计
- gcc提示库里文件未包含( “undefined reference to”)
- 高通安卓调试LCD几方面总结(一)
- Difference between TRUNCATE, DELETE and DROP commands
- 数字信号处理基础总结--7.28
- x86中内存段和段描述符
- youtube有意思视频收集
- IOS 国际化应用程序分析
- KMP 算法
- 开源项目扫盲--持续更新中
- 关系数据库知识总结
- MysqlDatabase , Change Password