Re: SQL update/insert statement.

Interestingly the ratio has to be
quite extreme before you should
do inserts before updates. The
break point is application and
data dependent, but could be
as high as 20 to 1.

A failed insert will hit insert into the table, generate rollback and redo,
walk the PK index, fail, rollback the
insert then load the name of the PK
that has been breached. (If you are
lucky the Pk will be the first index
walked - possibly you may walk and
insert on other indexes before the
PK fails, but I haven't tested that).

A failed update will walk an index
and miss - very little overhead, and
the index it walks will be one that
it has to walk to do the follow-up
insert anyway.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Connor McDonald wrote in message <38E09CD5.4E4D_at_yahoo.com>...
>
>or
>
>begin
> update table
> set x = y
> where ...
>
> if sql%rowcount = 0 then
> insert into ...
> end if;
>end;
>
>Ideally one would want to use the 'update first' or 'insert first' code
>dependant on which you expect to be higher frequency.
>
Received on Tue Mar 28 2000 - 13:07:16 CST