| 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
It looks to me that your developers are saying you need an unnecessary index
on B, using as their reasoning you have locking problems, when the locking
problems are caused by lacking an index on C. Why don't the developers
suggest putting an index on C's foreign key column where it belongs.
If you have a foreign key, you eliminate the unnecessary blocking by having an index with the foreign key column(s) as a leading column(s). You don't need an additional index just on the foreign key columns. You might try showing the developers what Oracle says about the subject. Do a search on Metalink for TM enqueues.
--Terry
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 - 12:04:39 CST
 |
 |