Difference between TRUNCATE, DELETE and DROP commands

最新推荐文章于 2025-12-03 13:04:04 发布

最新推荐文章于 2025-12-03 13:04:04 发布 · 65 阅读

文章标签：

本文介绍了在数据库管理中三种不同类型的SQL操作：DELETE用于删除表中的数据，TRUNCATE用于快速删除表中的所有数据且不回滚事务，而DROP则完全移除表及其定义，包括索引和约束。文章通过实例演示了如何使用这些命令及它们在事务处理和数据恢复方面的影响。

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。

hr@ORCL> select * from t;

        ID TEST
---------- ----------
         1 g
         3 d
         5 t

hr@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 d

hr@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 t

hr@ORCL> truncate table t;

Table truncated.

hr@ORCL> select * from t;

no rows selected

hr@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 exist


hr@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.