Plsql_ Standard Delete mode delete/drop/truncate difference and comparison (concept)

2014-06-02 Baoxinjian

First, General

1. Delete

2. Drop

3. Truncate

4. Example: How to delete the non-conforming 99.99 million of the 100 million data, keep the rest

Second, the specific analysis

1. Delete/drop/truncate The difference between the deletion method

(1). Deleting Data and table definitions

Truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index (indexed); Stored procedures/functions that depend on the table are preserved, but become invalid states.
(2). DML or DDL

The DELETE statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.
Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment and cannot be rolled back. Operation does not trigger trigger.
(3). Table Space

The DELETE statement does not affect the extent occupied by the table, and the high waterline (hi Watermark) remains in its original position.
The drop statement frees all the space occupied by the table
Truncate statement by default see space released to minextents extent unless reuse storage is used; Truncate will reset the high watermark (back to the beginning).
(4). Speed

General: drop> truncate > Delete
(5). Security

Be careful with drop and truncate, especially when there is no backup. Otherwise it's too late to cry
For use, to delete some data rows with delete, note the WHERE clause. The rollback segment should be large enough.
Want to delete the table, of course with drop
You want to keep the table and delete all the data. If it is unrelated to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.

2. Example: How to delete the non-conforming 99.99 million of the 100 million data, keep the rest
(1). Create a table temporarily
Create table as Select from * Where
(2). Truncate The original table, re-create the modified table
(3). Transfer the data in the temporary table to the newly created original table
Pros: No need to consider issues such as index space for tables