Re: When does Update become Delene then Insert, Ora817

I am not aware of any situation in which Oracle treats an update as anything other than an update. To the same block of the same extent of the same segment of the same tablespace. Even in a situation where the row chained to another block ... the original row would remain the start of the record.

Daniel Morgan

Kapal Mo wrote:

> Just doing some research on this matter with other databases like SQL Server
> and Sybase ASE. Both of them could treat an update as a delete then insert
> based on certain criteria. Example, on SQL Server (not sure what version)
> if I update a key field, it will do a delete then insert. Or if I update a
> lot of rows, the database would think it is more optimal to do a delete and
> insert.
>
> So in Oracle its different.
>
> "Andre van Winssen (andrew)" <andrew-no-spam-svp_at_info.nl> wrote in message
> news:3c73c854$0$218$4d4ebb8e_at_news.nl.uu.net...
> > No,
> > an update is an update. it will never fire any insert or delete triggers.
> > the rowid will not change either.
> > Imagine what it would cost to delete the row, including the removal of
> > asocitiated
> > index entries and space management work and then reinsert it back again,
> > including
> > the recreation of index entries and space management caised by thos
> > activities.
> > Not to mention PK-FK relationships.
> >
> > Andre
> >
> > "Kapal Mo" <kapalmo_at_nospam.hotmail.com> wrote in message
> > news:uIOc8.16877$X2.191092_at_nnrp1.uunet.ca...
> > > Clarification. Are you saying that if I updated all the columns of a
> row
> > > on a table, then Oracle will treat it as a delete then insert?
> > >
> > >
> > > "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> > > news:3C72EA80.6FD2CA3E_at_ci.seattle.wa.us...
> > > > None. Never. Nyet!
> > > >
> > > > Unless you have some burning desire to make a mess of performance and
> > > > scalability by messing with indexes and foreign keys. When updating
> you
> > > should
> > > > never update the entire row if updating specific fields will do the
> job.
> > > >
> > > > Daniel Morgan
> > > >
> > > >
> > > >
> > > > Kapal Mo wrote:
> > > >
> > > > > What situations should happen for an update be processed by the
> > database
> > > as
> > > > > a delete then insert?
> > > >
> > >
> > >
> >
> >
Received on Wed Feb 20 2002 - 17:33:05 CST