Update when the database processing situation ____ Database

Source: Internet
Author: User

Someone asked:

Today, I heard from an IBM person that the database was first deleted when it was updated, and then it was inserted, and I have some questions about it, and Oracle doesn't seem to be like this, please can you elaborate on the update process, update the primary key and do not update the primary key processing is the same. appreciated. In addition, the IBM people also said that update and insert is not an order of magnitude of the action, I think to be specific analysis of the same amount of data operations, update needs to locate, but how much slower.

For:

Other databases may be different from Oracle, but in the case of Oracle, update, if there is an update to the index field, delete the original index entry (not the actual delete data, just mark the row for deletion), insert the new index entry, and for the row itself, the update is to physically change the , if the length of the row increases to the current position cannot be accommodated, the position of the row is mentioned above the position of the top record of the block, and if it cannot be accommodated, a pointer is retained at the physical position of the original row, the row is migrated to the new block, and the reserved pointer is the location of the new blocks. At this point in the index rowid do not have to change, the query first find the reserved pointer, and then find the actual new position. If an update occurs after a row migration and then causes a migration, Oracle first looks at whether the block in the original location has the space to hold the line, if there is a move back, if there is no migration to the new block, modify the original pointer at the earliest blocks. That is, there will be no 2-pointer lookup to find the row. When the update, changes before and after the change of data are written to redo, before the change of data is also written back to the roll segment, after the change in data is applied to buffer.

If a table T (A,B,C) is assumed to exist
Update T set a = ... where B = ... and c = ...
The rollback segment only records the value of a before the change, the update is usually required to navigate through the index, otherwise the full table scan is very slow
And the insert is only to find a block inserted, the difference between the two may be very large, may not be an order of magnitude of time and resource consumption

For more detailed discussion please refer to: http://www.itpub.net/showthread.php?threadid=129524


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.