Re: Referential indexes

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Fri, 28 Jan 2005 02:03:37 -0800 (PST)
Message-ID: <20050128100337.25392.qmail@web52604.mail.yahoo.com>

Hi George,

I have them created by Oracle Designer by default. Then I am usually deleting them as obsolete when it is the leading column(s) in the PK(UK).

The reason is very simple, how more difficult will be to check the constraint on PK index instead of that additional index?
Probably nothing if they are both the same level. If they are not then probably it is small table then it is not worth it going into this discussion with DBA's.

I assume it is only important for big tables. That means one more index is some speed, memory, scalability overhead when doing DML and not saving anything in most cases.

Did not think about this for a long time. I adopted this as a regular thing to do while creating indexes on the FK columns.

Also there are scripts (I think at least from ixora) that are checking for these obsolete indexes that should be dropped.

Maybe some people has some bad behaviour experience when not having proper FK index.

Regards,
Zoran Martic

• "Leonard, George" <GLeonard_at_wesbank.co.za> wrote:

> 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_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
> 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
>
                

---

Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 28 2005 - 05:06:53 CST