Re: Do I need Index for FK and these Queries ?

Jim Smith wrote:

[snip]

>
> OK. Here we go. I'll try to use short sentences.

Long, short... it doesn't matter, so long as the information content is high enough.  

> The original question was do I need to index my foreign key (A.a,A.b).
> The answer to that is yes, because of the table locking issue.
>
> My supplementary question was that, given that (A.a,A.b) is already
> indexed as part of the primary key (A.a,A.b,A.c,A.d) would it still need
> its own index to avoid the locking problem.

Right. So actually your point was: "Howard, you do realise that his child table's already got an index on columns a,b,c and d... so are you sure he'd need another, separate index on columns a and b?"

To which the answer would have been: "No, sorry, I missed the fact that he already had an index on those two columns which would already serve duty for avoiding the locking issue. My original answer of "yes" was merely intended to point out that *an* index on those columns needed to exist. Not that a new one had to be created specifically".

I hope that's clear now, and I apologise for not having noticed the primary key index on table A. Speed reading, you know.

Specifically, you are correct to imply that a pre-existing index that includes the foreign key columns at its leading edge voids the need for another index to prevent the locking issue arising. Certainly in 9i and 10g at any rate (probably in 8i too).

Regards
HJR Received on Thu Oct 07 2004 - 07:27:35 CDT