Objective
When I used to do the project, I seldom paid attentionComparison of knowledge pointsdetails of the thing, just right today to have this opportunity, the Oracle in a few common functions to distinguish.
Grammar
- Delete from table_name
- TRUNCATE TABLE table_name
- drop table table_name
Difference
- Delete from behind can write conditions, truncate not, drop behind generally also do not write conditions.
- Delete from the record is a strip of deleted, each row of records will be removed into the log, and truncate delete the entire page at once, so the log only record page release, in short, delete from the update log, truncate BASIC does not update the log, the use of less transaction log space.
- Delete from the empty table, a blank page is retained, and truncate does not leave any pages in the table.
- When you execute a DELETE statement by using a row lock, the rows in the table are locked for deletion. Truncate always locks tables and pages instead of locking rows.
- If there is an identity-generated self-increment ID column, the delete from is still incremented from the last number, that is, the seed is unchanged, and the seed reverts to the original after truncate.
- The truncate does not trigger the delete, because the truncate operation does not record individual row deletions.
Summary
- 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; Stored procedures/functions that depend on the table are preserved, but become invalid states.
- The DELETE statement is DML, which is placed in the rollback segement and is not valid until the transaction is committed. If there is a corresponding trigger, the execution time will be triggered. Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment, cannot rollback, the operation does not trigger trigger.
- The DELETE statement does not affect the extent occupied by the table, and the high waterline (HI Watermark) retains its original position. It is clear that 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).
- Speed, in general: drop> truncate > Delete.
- Security, use drop and truncate carefully, 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. to keep the table and delete all the data, if it is not related to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete. 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.
Explain the differences between DELETE, TRUNCATE, and DROP in Oracle
