Someone asked:
Today, I listened to an IBM. I said that the database will first delete the original record, then insert, I have some questions about this, Oracle seems to be like this, please explain the process of Update, update the primary key and not update the primary key Is it the same? Greatful. In addition, the IBM people also say that Update and Insert are not a magnitude-level action, I think it is necessary to analyze, the operation of the same data, Update needs to be positioned, but how much is it slower?
answer:
Other databases may differ from Oracle, but for Oracle, when there is update, if there is an update of the index field, delete the original index entry (not real delete data, just in the row tag as delete) insert a new index entry For the update of the ROW itself, it is physically changed in the row. If the length of the row is increased to the current location, the line is not allowed to mention the top of the BLOCK, if the block is already unable to hold, Then, in the physical location of the original, a pointer is kept, the row is migrated to the new block, and the reserved pointer is the location of the new Block. At this time, RowID does not have to change. When you query, you will find the reserved pointer and then find the actual new location. If the UPDATE has caused migration after the migration, Oracle first looks at the original position of the block with space that accommodates the row. If there is a migration back, if you don't move to a new block, if you don't move to a new block, you will be able to move to the new block. pointer. That is to say, there is no search to find the line. When the update, the data after the change before and after the change is written to REDO, and the data is written before the change is written back, and the data is applied to Data Buffer. . If there is a table T (A, B, C) Update T set a = ... where b = ... and c = ..., only the value before the change in A is recorded, and when Update It usually needs to be positioned by an index, otherwise the full table scan is very slow and INSERT just finds a Block plug, the difference may be large, it may not be a quantitative time and resource consumption
For more detailed discussion, please refer to: http://www.itpub.net/showthread.php? Threadid = 129524
