Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Referential indexes

RE: Referential indexes

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Fri, 28 Jan 2005 07:56:36 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A972A8@exchsen0a1ma>


George,

Well with no indexes on the c table, I can see the deadlock problem easily occurring.

You update table b, it throws a full table lock on table c because of no index. Another update comes along and you get the b/c lock dance.

Put the index on table c and monitor.

Hope this helps.

Tom

-----Original Message-----
From: Leonard, George [mailto:GLeonard_at_wesbank.co.za] Sent: Friday, January 28, 2005 7:26 AM
To: Tim Gorman; oracle-l_at_freelists.org
Cc: Desplace, Laura
Subject: RE: Referential indexes

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_at_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_at_evdbt.com]=20 Sent: 28 January 2005 14:18 PM
To: oracle-l_at_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_at_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_at_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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 28 2005 - 07:59:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US