What is the essential difference between a database Drop command and a delete command

Source: Internet
Author: User
Tags rollback
Different points:
1. Differentiate from deleted content:
Truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table, the dependent constraint (constrain), the trigger (trigger), index (indexed); Stored procedures/functions that depend on the table are preserved, but become invalid states.
2. Differentiate from statement types:
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. Differentiate from the effect on the 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
The TRUNCATE statement frees space to minextents extent by default unless reuse storage is used, and truncate resets the high watermark (back to the beginning).
4. From the difference of speed:
General: drop> truncate > Delete

What is the essential difference between a database Drop command and a delete command

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.