R: Foreign Key indexes - when and how?

Carlo Petrone WZBKD6

R: Foreign Key indexes - when and how?
you can create an index if the dependent table do not have on first position
of primary key the coulumn of relation.

es. parent table pk Col1, Col2, Col3

fk Col1, Clo2, Col3

dependent Table pk Col1, Col2, Col3, Col4

in this case you connot create an index


-----Messaggio originale-----
Da: DB2 Data Base Discussion List [mailto:[login to unmask email]Per conto di S,
Sanjeev (CTS)
Inviato: martedì 2 gennaio 2001 13.01
A: [login to unmask email]
Oggetto: Re: 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
----------------------------------------------------------------------------
------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------








Sanjeev (CTS) S

FW: Foreign Key indexes - when and how?
(in response to Carlo Petrone WZBKD6)
** Resending this one as message got rejected because it exceeded 250
lines.

> Hi Carlo,
> In your example : fk Col1, Clo2, Col3, which are the part of Pk
> of dependent table is referenced by the parent table.I think the index on
> the foreign key already exists , however, it is the primary(unique) index.
> Most importantly , it will be used because the first few columns are same.
> Col4 is at the end.
>
> Please correct me if i am wrong.
>
> Regards.
> Sanjeev
>
>
> -----Original Message-----
> From: Carlo Petrone WZBKD6 [SMTP:[login to unmask email]
> Sent: Tuesday, January 02, 2001 5:57 PM
> To: [login to unmask email]
> Subject: R: Foreign Key indexes - when and how?
> Importance: High
>
> you can create an index if the dependent table do not have on first
> position
> of primary key the coulumn of relation.
>
> es. parent table pk Col1, Col2, Col3
>
> fk Col1, Clo2, Col3
>
> dependent Table pk Col1, Col2, Col3, Col4
>
> in this case you connot create an index
>
>
> -----Messaggio originale-----
> Da: DB2 Data Base Discussion List [mailto:[login to unmask email]Per conto di S,
> Sanjeev (CTS)
> Inviato: martedì 2 gennaio 2001 13.01
> A: [login to unmask email]
> Oggetto: Re: 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
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
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
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Henry J. Cobb

Re: Foreign Key indexes - when and how?
(in response to Sanjeev (CTS) S)
I usually find that the foreign key is used for certain joins.

So when it isn't the prefix of the primary key I find myself defining an
index on the foreign key eventually.

-HJC



[login to unmask email]

Foreign Key indexes - when and how?
(in response to Henry J. Cobb)
Thanks to all who responded. Sounds like the answers are... yes, they are
needed no matter what the rule, and no, they don't have to be the only
columns in the index, just the first ones.

Happy New Year,
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
-----------------------------------------------------

---------------------- Forwarded by Jackson Reavill/Tampa/Contr/IBM on
01/03/2001 03:50 PM ---------------------------

Jackson Reavill
12/28/2000 04:47 PM

To: DB2 Data Base Discussion List <[login to unmask email]>
cc:
From: Jackson [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
-----------------------------------------------------