Foreign Key indexes - when and how?

Sanjeev (CTS) S

Foreign Key indexes - when and how?
Hi Don,
Thanks for pointing out. If i can recall the Admin Guide context related to
indexes on foreign key then it says :
"Although not required but it is strongly recommended if the rows from the
parent table are deleted very often and the delete rule is defined".I think
tThis is mainly for the performance reason.

Thanks and Regards,
Sanjeev

> -----Original Message-----
> From: Don Alden [SMTP:[login to unmask email]
> Sent: Friday, December 29, 2000 9:07 PM
> To: [login to unmask email]
> Subject: Re: Foreign Key indexes - when and how?
>
> Gentlemen,
>
> This will be a quick answer because the DB2 Administration has
> everything
> you need to know
> about DB2 RI and I am attaching the Website URL for the manual. Just a
> quick tuning hint.
>
> The Foreign Key columns don't always need and index. If the child table
> has thousands of rows
> it is advisable that you use an Index to trigger a Matching Index scan.
> This is especially
> important for OLTP applications.
>
>
> http://www.s390.ibm.com/bookmgr-cgi/bookmgr.cmd/BOOKS/DSNAG0F6/CCONTENTS?S
> HE
> LF=EZ239317
>
>
> Good Luck,
> Don Alden
> DB2 DBA :-)
>
>
> -----Original Message-----
> From: S, Sanjeev (CTS) [mailto:[login to unmask email]
> Sent: Thursday, December 28, 2000 10:09 PM
> To: [login to unmask email]
> Subject: Re: Foreign Key indexes - when and how?
>
>
> Hi Jay,
> As we know idexes are used to locate the data for either selecting it,
> updating it or deleting it. This way we can say that indexes are required
> on
> the foreign key irrespective of the kind of RI rules implemented because
> for
> any rule DB2 has to locate the rows for either restricting or deleting it.
> Regarding the order of the foreign key index, it should exactly match
> the
> parent key. The first column should match to make it matching index scan
> for
> DB2. However , i think you can have the additional columns at the end in
> the
> foreign key indexes.
>
> HTH
> Regards,
> Sanjeev
>
> > -----Original Message-----
> > From: Jackson Reavill [SMTP:[login to unmask email]
> > Sent: Friday, December 29, 2000 3:17 AM
> > To: [login to unmask email]
> > Subject: Foreign Key indexes - when and how?
> >
> > Happy Holidays!
> >
> > I searched the archives, but couldn't find what I'm looking for. Which
> > is... What are the guidelines in defining foreign key indexes? I know
> > they are needed when the rule is delete cascade, but what about
> restrict?
> > Is one needed to make the check more efficient? What about the columns
> in
> > the index? Do they need to match the foreign key exactly in number of
> > columns and order or can they just be the first column(s) of a
> > multi-columned index?
> >
> > As always, any advice is greatly appreciated,
> > Jay
> >
> >
> > Jay Reavill
> > [login to unmask email]
> > IBM Global Services
> > Tampa, Florida
> > Tel: (813) 801-7303, Tie Line 8-427-7303
> > -----------------------------------------------------
> > Happiness is not around the corner.
> > Happiness is the corner.
> > - Bavarian Motor Works
> > -----------------------------------------------------
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can
> >
> --------------------------------------------------------------------------
> --
> -------------------------------------------------------------
> --------------------------------------------------------------------------
> --
> -------------------------------------------------------------
> This e-mail and any files transmitted with it are for the sole use
> of the intended recipient(s) and may contain confidential and privileged
> information.
> If you are not the intended recipient, please contact the sender by reply
> e-mail and
> destroy all copies of the original message. Any unauthorised review, use,
> disclosure,
> dissemination, forwarding, printing or copying of this email or any action
> taken in
> reliance on this e-mail is strictly prohibited and may be unlawful.
>
> Visit us at http://www.cognizant.com
> --------------------------------------------------------------------------
> --
> ------------------------------------------------------------
> --------------------------------------------------------------------------
> --
> ------------------------------------------------------------
>
>
>
> the
>
>
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------