Re: When does Update become Delene then Insert, Ora817

Hi Kapal,

I think there is a misunderstanding here between a semantic DELETE/INSERT vs a physical DELETE/INSERT.
An example where a semantic UPDATE may turn into a physical DELETE/INSERT would be if the
table is split along certain lines, such as range-partitioning. In SQL Server this would happen in a federated system (not sure if SS is smart enough to avoid triggers there, RI - oh well ... ), Infact MS had to reissue their first federated TPC-C benchmark because SS2000 originally lacked the ability to update the partitioning key.
Another example may (?) be a clustered table.

On Informix updating a row in a frangmented table can cause migration between fragments.
On DB2 EEE updating of the partitioning key can cause a record to migrate from one node to another.

It's likely that range partitioning in Oracle will cause similar behaviour under the hood.
But that is not mine to answer in this group of course :-)

Either way the user sees only an UPDATE (no unexpected triggers, no RI trouble...)

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada