| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Referential indexes
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
-----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
>
_________________________________________________________________________= __________________________
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-lReceived on Fri Jan 28 2005 - 07:59:11 CST
 |
 |