Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 19413 invoked from network); 8 Aug 2008 06:24:57 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by air964.startdedicated.com with SMTP; 8 Aug 2008 06:24:57 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B2C6A8D6117; Fri, 8 Aug 2008 07:24:52 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13113-04-3; Fri, 8 Aug 2008 07:24:52 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7122F8D61DC; Fri, 8 Aug 2008 07:24:51 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 08 Aug 2008 07:22:54 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 272998D5CDF for ; Fri, 8 Aug 2008 07:22:54 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12121-05-2 for ; Fri, 8 Aug 2008 07:22:54 -0400 (EDT) Received: from kbspmxp1.starhub.net.sg (kbsmtao183.starhub.net.sg [203.116.2.183]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E54258D5D11 for ; Fri, 8 Aug 2008 07:22:52 -0400 (EDT) Received: from southgate.starhub.net.sg (southgate.starhub.net.sg [203.117.3.5]) by kbspmxp1.starhub.net.sg (8.13.7+Sun/8.13.7) with ESMTP id m78BMJ8T024825 for ; Fri, 8 Aug 2008 19:22:21 +0800 (SGT) X-SBRS: 3.6 X-HAT: Message received through Sender Group RELAY,Policy $RELAY applied. Received: from kbsmtao2.starhub.net.sg ([203.116.2.167]) by surfgate2.starhub.net.sg with ESMTP; 08 Aug 2008 19:22:48 +0800 Received: from windows01 ([218.186.247.5]) by kbsmtao2.starhub.net.sg (Sun Java System Messaging Server 6.2-9.07 (built Oct 18 2007)) with ESMTPP id <0K5A00D4F4XZ66G0@kbsmtao2.starhub.net.sg> for oracle-l@freelists.org; Fri, 08 Aug 2008 19:22:48 +0800 (SGT) Date: Fri, 08 Aug 2008 19:22:47 +0800 From: Tanel Poder Subject: RE: Rollback per transaction %:73 %? In-reply-to: <489146BE.6040000@power.com.pl> To: "'Wiktor Moskwa'" Cc: "'ORACLE-L'" Message-id: MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.5512 Content-Type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT References: <69E1360E54B50C4A828A136C158E474204FC9F0222@BLRKECMBX02.ad.infosys.com> <69E1360E54B50C4A828A136C158E474204FC9F065F@BLRKECMBX02.ad.infosys.com> <489146BE.6040000@power.com.pl> X-archive-position: 9993 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: tanel.poder.003@mail.ee Precedence: normal Reply-to: tanel.poder.003@mail.ee List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain 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@power.com.pl] > Sent: Thursday, July 31, 2008 13:00 > To: tanel.poder.003@mail.ee > 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 > > -- http://www.freelists.org/webpage/oracle-l