RE: Referential indexes
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'GLeonard@xxxxxxxxxxxxx'" <GLeonard@xxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 28 Jan 2005 07:51:43 -0500
George,
I agree with Nuno - developers are wrong. As long as there is an index on
the child column (either PK or a non-unique index), then it does not matter.
As a test, have your friend try and create the additional index. Oracle may
stop him/her saying that the column is already indexed.
If the index is created, run a simple explain plan against a query using the
child table. The PK/unique index will *always* be used before the
non-unique index.
Good Luck!
Tom
-----Original Message-----
From: Leonard, George [mailto:GLeonard@xxxxxxxxxxxxx]
Sent: Friday, January 28, 2005 4:51 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: Desplace, Laura
Subject: Referential indexes
Hi all
Got this query from a developer, or actually they are actually arguing
with the DBA team.
Table A - master, acc number field also only column in PK
Table B - Child, acc Number Field acc number is the first
column in PK, PK contains 2 more columns.
DBA's are saying we don't need a normal non unique stand alone index on
the acc Number field for table B.
Developers want a separate index,
COMMENT?
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
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 ________________________________
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
