Oracle path (2)-truncate, delete, drop

Similarities and differences between truncate, delete, and drop

Note: The delete statement is a delete statement without the WHERE clause.

Similarities:

Truncate, delete without WHERE clause, and drop both delete table data.

Differences:

1. truncate and delete: delete only data. Do not delete the table structure (Definition)

The drop statement deletes the constrain, trigger, and index constraints on the table structure );

Stored Procedures/functions dependent on the table will be retained, but will change to the invalid state.

2. The delete statement is DML, which is placed in the rollback segement and takes effect only after the transaction is committed;

If a trigger exists, the trigger is triggered during execution. truncate. Drop is DDL, and the operation takes effect immediately,

The original data is not stored in rollback segment and cannot be rolled back. Trigger is not triggered.

3. The delete statement does not affect the extent used by the table, and the high watermark keeps the original position unchanged.

Obviously, the drop statement releases all the space occupied by the table. The truncate statement is released to the minextents extent by default,

Unless reuse storage is used; truncate will reset the high water line (back to the beginning ).

4. Speed, in general: Drop>; truncate>; Delete

5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, you will not be able to use them when crying,

To delete some data rows, use Delete. Note that the WHERE clause must be included. The rollback segment must be large enough. To delete a table,

Of course, you can use drop to delete all the data that you want to keep the table. If it is irrelevant to the transaction, use truncate.

If it is related to the transaction, or you want to trigger the trigger, or use Delete. If it is to sort the fragments in the table,

You can use truncate to keep up with reuse Stroage, and then re-import/insert data.