From oracle-l-bounce@freelists.org Fri Jan 28 07:28:54 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j0SDSrBp018861 for ; Fri, 28 Jan 2005 07:28:53 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j0SDSrem018857 for ; Fri, 28 Jan 2005 07:28:53 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 27293674C6; Fri, 28 Jan 2005 07:28:04 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04268-04; Fri, 28 Jan 2005 07:28:04 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9520F66DC6; Fri, 28 Jan 2005 07:28:03 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Subject: RE: Referential indexes Date: Fri, 28 Jan 2005 14:26:03 +0200 Message-ID: <1831A554E8800049B6B970790D2513C001C360E8@fnbkrkmx01.fnb.co.za> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Referential indexes Thread-Index: AcUFM2IW4xCJANKRRsurel472LtYegAADGcg From: "Leonard, George" To: "Tim Gorman" , Cc: "Desplace, Laura" X-OriginalArrivalTime: 28 Jan 2005 12:26:04.0310 (UTC) FILETIME=[88E56360:01C50534] X-archive-position: 15402 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: GLeonard@wesbank.co.za Precedence: normal Reply-To: GLeonard@wesbank.co.za X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at example.com X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: We are getting blocking locks on a table B. The developers came up with the idea that since the child table (B) does not have a dedicated non unique index on the acc num column (although the column is the first column of the PK) that this is causing the blocking locks. B.accnum=3D A.accnum We are saying column is already indexed and this is not the problem. We/DBA's have however found that this table (b) is part of a 3 layer set of tables actually. c.accnum=3D>b.accnum=3D>a.accnum They are updating B (All fields via a Form including the acc num colum) and Table C actually references B. C got a Foreign Key to B on Acc Num. Problem we found was that C does not have ANY index on the Acc num column. George =20________________________________________________ George Leonard Oracle Database Administrator New Dawn Technologies @ Wesbank E-mail:gleonard@wesbank.co.za =20 You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! =20 -----Original Message----- From: Tim Gorman [mailto:tim@evdbt.com]=20 Sent: 28 January 2005 14:18 PM To: oracle-l@freelists.org Cc: Desplace, Laura; Leonard, George Subject: Re: Referential indexes Please ask the developers: exactly what problem are they seeing? Please describe the failure in detail, describe the symptoms of the problem? Otherwise, it's like claiming murder without a corpse as evidence. on 1/28/05 2:51 AM, Leonard, George at GLeonard@wesbank.co.za wrote: > Hi all >=20 >=20 > Got this query from a developer, or actually they are actually arguing > with the DBA team. >=20 > Table A - master, acc number field also only column in PK >=20 > Table B - Child, acc Number Field acc number is the first > column in PK, PK contains 2 more columns. >=20 > DBA's are saying we don't need a normal non unique stand alone index on > the acc Number field for table B. >=20 > Developers want a separate index, >=20 > COMMENT? >=20 > George > =3D20________________________________________________ > George Leonard > Oracle Database Administrator > New Dawn Technologies @ Wesbank > E-mail:gleonard@wesbank.co.za > =3D20 > You Have The Obligation to Inform One Honestly of the risk, And As a > Person > You Are Committed to Educate Yourself to the Total Risk In Any Activity! > Once Informed & Totally Aware of the Risk, > Every Fool Has the Right to Kill or Injure Themselves as They See Fit! > =3D20 > ge/oracle-l > ________________________________________________________________________ _=3D > __________________________ >=20 >=20 > The views expressed in this email are, unless otherwise stated, those of =3D > the author and not those > of the FirstRand Banking Group an Authorised Financial Service Provider o=3D > r its management. > The information in this e-mail is confidential and is intended solely for=3D > =3D20the addressee. > Access to this e-mail by anyone else is unauthorised. > If you are not the intended recipient, any disclosure, copying, distribut=3D > ion or any action taken or=3D20 > omitted in reliance on this, is prohibited and may be unlawful. > Whilst all reasonable steps are taken to ensure the accuracy and integrit=3D > y of information and data=3D20 > transmitted electronically and to preserve the confidentiality thereof, n=3D > o liability or=3D20 > responsibility whatsoever is accepted if information or data is, for what=3D > ever reason, corrupted=3D20 > or does not reach its intended destination. >=20 > =3D20 ________________________________ > -- > http://www.freelists.org/webpage/oracle-l >=20 _________________________________________________________________________= __________________________ The views expressed in this email are, unless otherwise stated, those of = the author and not those of the FirstRand Banking Group an Authorised Financial Service Provider o= r its management. The information in this e-mail is confidential and is intended solely for= =20the addressee. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribut= ion or any action taken or=20 omitted in reliance on this, is prohibited and may be unlawful. Whilst all reasonable steps are taken to ensure the accuracy and integrit= y of information and data=20 transmitted electronically and to preserve the confidentiality thereof, n= o liability or=20 responsibility whatsoever is accepted if information or data is, for what= ever reason, corrupted=20 or does not reach its intended destination. =20 ________________________________ -- http://www.freelists.org/webpage/oracle-l