Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: When does Update become Delene then Insert, Ora817
I think you're right.
In ss (from memory) I think there were three possible update mechanisms:
update in place - this is why there was a performance advantage to using
char columns and not varchar
update rewrite - I think this was like an oracle update where the row is
reformatted and rewritten to the block
delete/insert - the record was physically deleted and then re-inserted e.g.
when the primary key of clustered table was updated. Presumably, this is
what happens in Oracle when row-migration between partitions occurs.
My apologies if this is not exactly right, but I think it explains the issue.
Serge Rielau <srielau_at_ca.ibm.com> wrote in message
news:3C7437D6.92670780_at_ca.ibm.com...
> 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
>
>
Received on Thu Feb 21 2002 - 02:23:13 CST