The difference between delete, truncate, and drop in Oracle

the difference between delete, truncate, and drop in OracleTags: deleteoracletable storage2012-05-23 15:12 7674 People read comments (0) favorite reports Classification:Oracle (2)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

First, delete

1, delete is DML, when you perform a delete operation, each time a row is deleted from the table, and the delete operation of the row is recorded in the Redo and undo table spaces for rollback (rollback) and redo operations, but be aware that the tablespace is large enough A manual commit (commit) action is required to take effect, and you can undo the operation by rollback.

2. Delete can delete the data in the table that satisfies the criteria, or delete all records in the table if the WHERE clause is not specified.

3. The DELETE statement does not affect the extent occupied by the table, and the high waterline (higher watermark) keeps the original position unchanged.

Second, truncate

1, Truncate is DDL, will be implicitly committed, so, cannot rollback, does not trigger trigger.

2. Truncate will delete all records in the table and will reset the high watermark and all indexes, freeing the space to minextents extent by default, unless reuse storage is used. Logs are not logged, so the execution is fast, but cannot be undone by rollback (if a table is accidentally truncate off, it can be restored, but cannot be recovered by rollback).

3. For a table referenced by a foreign KEY (ForeignKey) constraint, you cannot use TRUNCATE TABLE, and you should use a DELETE statement without a WHERE clause.

4. Truncatetable cannot be used for tables that participate in an indexed view.

Three, drop

1. Drop is DDL and is implicitly committed, so it cannot be rolled back and triggers will not be triggered.

2. Drop statement deletes the table structure and all data, and frees all the space occupied by the table.

3. The drop statement will delete the constraints that the table's structure relies on, triggers, indexes, stored procedures/functions that depend on the table will be preserved, but become invalid states.

Summarize:

1, in the speed, generally,drop> truncate > delete.

2, in the use of drop and truncate must pay attention, although can be restored, but in order to reduce the trouble, still need to be cautious.

3, if you want to delete some of the data with delete, pay attention to the WHERE clause, the rollback segment is large enough, if you want to delete the table, of course, drop, if you want to keep the table and all the data is deleted, if it is not related to the transaction, with the truncate, if the transaction, or want to trigger trigger, or delete, or if you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

The difference between delete, truncate, and drop in Oracle