主键更新与主键删除+插入

Question

我听说过一些传言，当主键列中的值发生变化时，应该删除行，然后用新值插入行，而不是只更新列。

当主键更新而不是删除/插入时，是否会影响性能？

Answer 1

当您更新主键时：

• 更新支持索引。如果
• 是外键，将检查其是否为childs。

但如果您执行delete和insert，则for delete将执行这两个点，而for insert将更新索引。所以执行delete和insert没有任何好处。或者据我所知没有。

简而言之，做两个操作比做一个操作更糟糕。不包括删除是最困难的操作。

Answer 2

虽然我坚信你应该设计你的物理模型，使外键是as stable as possible的，但有时你真的需要更新一组键，例如由于重组。

让我们比较一下简单的更新和主键的delete+insert在数据方面发生了什么。我们假设您的表被组织为堆(默认)，并且现在表上没有外键或其他索引：

1. simple update

- _data modified_: the row data of the table will be modified directly in the block. The primary key columns are often small so it is unlikely that the rows will be migrated. The primary key index acts as a regular index: an update to the key will cause the entry pointing to the old key to be deleted while a new entry pointing to the new value will be inserted.
- _redo entries_: redo for the update will contain the physical changes operated on the data: a single column update (small redo) and an index delete + insert.
- _undo entries_: the undo will contain the old column value (small undo) and the reverse insert + delete of the index. 

​

1. delete+insert

- _data modified (redo)_: Oracle will have to physically delete the row and insert another row. Deleting a row is a simple operation from a direct data point of view: the block containing the row is located and the row is marked as deleted. If the block still contains a sufficient amount of data (other non deleted rows), it will be left as is. If not (below the `PCTUSED` value of the segment) it will be added to a list of block eligible for inserts. An insert causes more redo comparatively since the values of all columns need to be recorded. Of course, deleting the old row and inserting the new one will lead to the deletion of the old index entry and the creation of the new index entry (same as above).
- _undo entries_: the delete operation needs to record the value of all columns of the old row. The insert on the other hand is protected in undo as a simple delete.

​

如果表有其他索引，情况会更糟，因为每个索引都需要为一个delete+insert维护(而主键更新不会影响这些索引，除非列重叠)。

如果有键引用了这个表，那么在这两种情况下都会遇到引用问题。如果该表引用了其他表，那么在delete+insert中也会有更多的工作(除非引用再次基于主键列，在这种情况下，它或多或少是相同的)。

结论：由于insert+delete影响基表的所有列，因此它将比简单的update引起更多的工作:更多的撤消、更多的重做以及对所有索引(而不仅仅是主键索引)的两个操作。

如果您的表是按索引组织的，则工作量将大致相同，因为行将被物理移动，但我非常确定单个更新仍将比两个单个操作更有效(因为每个操作都涉及开销)。

Answer 3

最大的性能和并发影响是在子表FKs上没有索引时。在这种情况下，Oracle没有其他选择，只能锁定整个子表，对其进行扫描以验证引用完整性。

也许这在某种程度上与主键上的更新和删除/插入混淆了。也许是因为Oracle支持CASCADE ON DELETE，但不支持UPDATE。

当您确实需要更新时，请不要使用delete+insert。