Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 02CC1196057B for ; Fri, 8 Aug 2014 21:31:40 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Fri, 8 Aug 2014 21:31:39 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CB4E02C0D0; Fri, 8 Aug 2014 15:31:37 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org 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 ZiuVAo8-KNxa; Fri, 8 Aug 2014 15:31:37 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D34782C85C; Fri, 8 Aug 2014 15:30:56 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 08 Aug 2014 15:30:15 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A02AB2C808 for ; Fri, 8 Aug 2014 15:30:15 -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 5KLEImBYHJnT for ; Fri, 8 Aug 2014 15:30:15 -0400 (EDT) Received: from troll.tpk.net (mail.tpk.net [216.107.198.11]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 228472C80A for ; Fri, 8 Aug 2014 15:29:44 -0400 (EDT) Received: from mwf4500 (c-50-133-134-194.hsd1.ma.comcast.net [50.133.134.194]) by troll.tpk.net (8.14.2/8.12.11) with ESMTP id s78JThHw009624 for ; Fri, 8 Aug 2014 15:29:44 -0400 From: "Mark W. Farnham" To: "'ORACLE-L'" References: <7DCF5A73984B85409A1FB7B957E590ED8446504C@EXMB3RSC.roswellpark.org> <0BDF2A25A09ADD40908745EEFC0A0FB6021C1620@HKJUMXMB103B.zone1.scb.net> <1E24812FBE5611419EFAFC488D7CCDD126EDE330@G6W2491.americas.hpqcorp.net> In-Reply-To: <1E24812FBE5611419EFAFC488D7CCDD126EDE330@G6W2491.americas.hpqcorp.net> Subject: RE: rowid value Date: Fri, 8 Aug 2014 15:29:40 -0400 Message-ID: <17d701cfb33f$19d3dba0$4d7b92e0$@rsiz.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_17D8_01CFB31D.92C23BA0" Content-Language: en-us X-archive-position: 55869 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: mwf@rsiz.com 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 ------=_NextPart_000_17D8_01CFB31D.92C23BA0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable If someone tells me they have a duplicate rows, I think: All columns the = same If someone tells me they have a unique key violation I would not call = the rows duplicates nor assume they are without investigation. If someone tells me they have duplicates, then I wonder (and ask). =20 Agreed these are very different cases. =20 Import jobs done twice with no constraint are duplicate rows. Application duplicate control without constraints could be either. =20 If you have PK and UKs defined, they don=E2=80=99t happen in your = tables, and if you=E2=80=99ve got at least one, there will be no = duplicate rows. =20 mwf =20 From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of Powell, Mark Sent: Friday, August 08, 2014 2:55 PM To: ORACLE-L Subject: RE: rowid value =20 Jared said, =E2=80=9CIdentifying which 'duplicate' to delete it [sic] = not always straight forward.=E2=80=9D =20 No it isn=E2=80=99t since the non-key columns may differ and in some = cases you actually should determine which row to save based on the = non-key data. Worse is when you discover you need some data from = multiple rows. =20 =20 =20 =20 From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jared Still Sent: Friday, August 08, 2014 2:23 PM To: kibeha@gmail.com Cc: Hemant-K.Chitale@sc.com; Brian Zelli; ORACLE-L Subject: Re: rowid value =20 =20 On Wed, Aug 6, 2014 at 1:15 AM, Kim Berg Hansen = wrote: If the idea of deleting the "higher" ROWID is to delete the "newest" = row, that is not at all certain. But if the rows are identical = duplicates, that shouldn't matter ;-) Probably this is already stated somewhere in this thread, but it bears = repeating. =20 Duplicate refers to PK/UK, not the row. =20 Identifying which 'duplicate' to delete it not always straight forward. =20 Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist Sr Oracle DBA at Pythian Pythian Blog http://www.pythian.com/blog/author/still/ Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com ------=_NextPart_000_17D8_01CFB31D.92C23BA0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

If someone tells me they have a duplicate rows, I think: All columns = the same

If someone tells me they have a unique key violation I would not call = the rows duplicates nor=C2=A0 assume they are without = investigation.

If someone tells me they have duplicates, then I wonder (and = ask).

 

Agreed these are very different cases.

 

Import jobs done twice with no constraint are duplicate = rows.

Application duplicate control without constraints could be = either.

 

If you have PK and UKs defined, they don=E2=80=99t happen in your = tables, and if you=E2=80=99ve got at least one, there will be no = duplicate rows.

 

mwf

 

From:= = oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Powell, Mark
Sent: Friday, August 08, 2014 = 2:55 PM
To: ORACLE-L
Subject: RE: rowid = value

 

Jared said, =E2=80=9CIdentifying which 'duplicate' to delete it [sic] = not always straight forward.=E2=80=9D

 

No it isn=E2=80=99t since the non-key columns may differ and in some = cases you actually should determine which row to save based on the = non-key data.  Worse is when you discover you need some data from = multiple rows.  

 

 

 

From:= = oracle-l-bounce@freelists.o= rg [mailto:oracle-l-bounce@free= lists.org] On Behalf Of Jared Still
Sent: Friday, = August 08, 2014 2:23 PM
To: kibeha@gmail.com
Cc: Hemant-K.Chitale@sc.com; = Brian Zelli; ORACLE-L
Subject: Re: rowid = value

 

 

On Wed, = Aug 6, 2014 at 1:15 AM, Kim Berg Hansen <kibeha@gmail.com> wrote:

If the idea of deleting the "higher" ROWID = is to delete the "newest" row, that is not at all certain. But = if the rows are identical duplicates, that shouldn't matter = ;-)


Probably this is = already stated somewhere in this thread, but it bears = repeating.

 

Duplicate refers to PK/UK, not the = row.

 

Identifying which 'duplicate' to delete it not always = straight forward.

 



Jared Still
Certifiable Oracle DBA and Part Time = Perl Evangelist

Sr Oracle DBA at = Pythian

------=_NextPart_000_17D8_01CFB31D.92C23BA0-- -- http://www.freelists.org/webpage/oracle-l