Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: When does Update become Delene then Insert, Ora817

Re: When does Update become Delene then Insert, Ora817

From: Keith Boulton <kboulton_at_ntlworld.com>
Date: Thu, 21 Feb 2002 08:23:13 -0000
Message-ID: <j92d8.1936$hM6.116901@news6-win.server.ntlworld.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US