Re: select + insert vs. insert and catching exception

No.

The fact that when you insert and fail Oracle has to rollback the attempted insert and process the constraint failure by calling a select on con$ to translate the constraint number into the constraint name so that it can be reported into the error message.

(A volume test then demonstrates the point, of course - but pure clock tests should always be backed up by analysis of activity).

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html








Christoph Seidel wrote in message ...


>the routine is called very often and the insert is done very seldom

>(10000:1)

>

>what gives you the certainty that the select is better? a load test?

>

>

>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

>news:amvn1b$k24$1$8300dec7_at_news.demon.co.uk...

>>

>> How often is it going to happen ?

>> How often is it likely to fail on insert ?

>>

>> If the task runs rarely, who cares about a

>> marginal overhead.  If the task runs very

>> frequently and the select usually succeeds

>> then do the select first.  If the task runs

>> very frequently and the insert usually succeeds

>> (at least 19 times out of 20) then consider

>> doing the insert first.

>>

>> Inserting and getting a constraint breached

>> is much more expensive than doing a single

>> select which returns no rows.

>>

>> (Would you be doing an update if the select

>> succeeds ? If so why not:

>>     update

>>     if no rows updated

>>         insert

>>

>> --

>> Regards

>>

>> Jonathan Lewis

>> http://www.jlcomp.demon.co.uk

>>

>> Next Seminar dates:

>> (see http://www.jlcomp.demon.co.uk/seminar.html )

>>

>> ____England______September 24/26, November 12/14

>>

>> ____USA__________November 7/9 (MI), 19/21 (TX)

>>

>> The Co-operative Oracle Users' FAQ

>> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>>

>>

>>

>>

>>

>>

>>

>>

>> Christoph Seidel wrote in message ...

>> >what is better in respect of performance?

>> >

>> >select by primary key, if not found insert

>> >

>> >or

>> >

>> >insert and catch exception if primary key exists

>> >

>> >the table is quite small, less than 100 rows

>> >

>> >

>>

>>

>

>