Foreign Key indexes - when and how?

[login to unmask email]

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



Tim Lowe

Re: Foreign Key indexes - when and how?
(in response to damcon2@US.IBM.COM)
Jay,
Regardless of whether the rule is cascade or restrict, the index is needed
for performance to determine if children of the rule exist.
The first columns of the index must match the referential integrity rule in
exactly the same order. They can, and most often are, the first columns of
a multi-column index.

I hope this helps.

Thanks,
Tim



Jackson
Reavill To: [login to unmask email]
<[login to unmask email] cc:
BM.COM> Subject: Foreign Key indexes - when and how?
Sent by: DB2
Data Base
Discussion
List
<[login to unmask email]
OM>


12/28/2000
03:47 PM
Please
respond to
DB2 Data Base
Discussion
List






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








Sanjeev (CTS) S

Re: Foreign Key indexes - when and how?
(in response to Tim Lowe)
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
> -----------------------------------------------------
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
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
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



[login to unmask email]

Re: Foreign Key indexes - when and how?
(in response to Sanjeev (CTS) S)
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?SHE
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
> -----------------------------------------------------
>
>
>
>
>
----------------------------------------------------------------------------
-------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------
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
----------------------------------------------------------------------------
------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------