RE: Rollback per transaction %:73 %?
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: "'Wiktor Moskwa'" <wmoskwa@xxxxxxxxxxxx>
- Date: Fri, 08 Aug 2008 19:22:47 +0800
Hi Wiktor,
In theory you should choose the approach based on which case occurs mostly -
if you are expecting 99% of inserts to fail as corresponding rows already
exist in database (and need to be updated instead) then better start with
update and if 0 rows found then insert. And vice versa, if you expect to
insert 99% of rows and only update 1% then better start with insert (and if
it fails then update).
However there is one practical issue with the latter approach:
If you use "INSERT->if failed THEN UPDATE", then every time the insert fails
Oracle will go and fetch the constraint/index name from data dictionary for
returning the constraint name as seen below:
-- cause a constraint violation:
SQL> insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C001667) violated
-- sql trace output:
PARSING IN CURSOR #3 len=119 dep=2 uid=0 oct=3 lid=0 tim=969961722
hv=3286148528 ad='6aae9e04'
select c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# = cd.con#
and cd.enabled = :1 and c.owner# = u.user#
This will cause a recursive query every time your insert fails due that
constraint. Which may mean lots of unnecessary logical IOs every insert.
--
Regards,
Tanel Poder
http://blog.tanelpoder.com
> -----Original Message-----
> From: Wiktor Moskwa [mailto:wmoskwa@xxxxxxxxxxxx]
> Sent: Thursday, July 31, 2008 13:00
> To: tanel.poder.003@xxxxxxx
> Cc: 'ORACLE-L'
> Subject: Re: Rollback per transaction %:73 %?
>
>
> > Couple reasons that come into my mind are:
> >
> > 1) Bad application design / coding practices (e.g. insert
> -> if failed
> > -> then update)
> > [..]
>
> I'm just curious why do you consider it a bad practice?
>
> Thanks,
> Wiktor Moskwa
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
