primary key update vs primary key deletion + insertion

Question

I have heard some rumours that when value in primary key column is changed, then row should be deleted then inserted with new value rather then column just updated.

Are there any performance implications when primary key is updated and not deleted/inserted?

Answer 1

When you update the primary key:

• the supporting index is updated.
• if it is a foreign key will be checked for childs.

But if you do a delete and an insert, for delete both points are performed and for insert the index is updated. So no gain to do a delete and an insert. Or none that I'm aware of.

Shortly, it is worse to do two operations instead of one. Not counting that delete is the hardest operation.

Answer 2

While I firmly believe that you should design your physical model so that the foreign keys are as stable as possible, sometimes you really need to update a set of keys, for example due to reorganization.

Let's compare what happens data side between a simple update and a delete+insert of a primary key. We'll suppose that your table is organized as a heap (default) and for now that there are no foreign keys nor other indexes on the table:

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.
     
     

2. 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.

If the table had other indexes, it would be even worse because each index would need to be maintained for a delete+insert (whereas they are left untouched by a primary key update unless the columns overlap).

If there are keys referencing this table, you'll run into referential problems in both cases. If the table references other tables, again you'll have more work in a delete+insert (unless again the reference is based on the primary key column in which case it would be more or less the same).

Conclusion: since the insert+delete affects all columns of the base table, it will cause a lot more work than a simple update: more undo, more redo and the two operations on all indexes (instead of the primary key index only).

If your table is index-organized, the amount of work would be more or less the same because the row will be moved physically, but I'm pretty sure that a single update will still be more efficient than two single operations (because each operation involves overhead).

Answer 3

The biggest performance and concurrence impact is when you do NOT have index on child table FKs. In such a case Oracle has no other option then to LOCK the whole child table a scan it to validate referential integrity.

Maybe this somehow got confused with UPDATE vs. DELETE/INSERT on primary key. Maybe because Oracle supports CASCADE ON DELETE but not on UPDATE.

Simply do not use delete+insert when you do need update.