Re: Referential indexes
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 Jan 2005 08:57:07 -0800
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
----- Original Message -----
From: "Leonard, George" <GLeonard@xxxxxxxxxxxxx>
To: "Tim Gorman" <tim@xxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Cc: "Desplace, Laura" <LDesplace@xxxxxxxxxxxxx>
Sent: Friday, January 28, 2005 4:26 AM
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@xxxxxxxxxxxxx
=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@xxxxxxxxx]=20
Sent: 28 January 2005 14:18 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx
> =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 ________________________________
> --
> //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 ________________________________
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
