Re: When does Update become Delene then Insert, Ora817

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 - 16:31:16 CST