Difference between delete and truncate: deletetruncate

Source: Internet
Author: User

Difference between delete and truncate: deletetruncate

Similarities: The truncate function is the same as the delete statement without the WHERE clause: both delete all rows in the table. Use truncate with caution. After deletion, it will disappear.

1. delete: deleting a "Table Record" will record the operation in the log, and the deleted data can be restored through transaction rollback.

Truncate: deleting "table records" cannot be recovered.

 

2. delete: delete a row each time and record one row in the transaction log.

Truncate: deletes data from the data page used to release table data, and only records the release of pages in transaction logs.

Therefore, truncate is faster than delete.

3. delete: delete the content. Release the space without deleting the definition.
Truncate: Delete the content. Do not delete the definition or release the space.

 

4. delete: the statement does not affect the extent used by the table. The high w2atermark (high waterline) keeps the original position unchanged.
Truncate: by default, the statement releases the space to the extent of minextents, unless reuse storage is used; truncate resets the high water line (back to the beginning ).

 

To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.

To delete a table, use drop

Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use delete.

Related Article

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.