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

In message <416524a7$0$20129$afc38c87_at_news.optusnet.com.au>, Howard J. Rogers <howardjr_at_dizwell.com> writes
>Jim Smith wrote:
>
>> In message <4165093c$0$4310$afc38c87_at_news.optusnet.com.au>, Howard J.
>> Rogers <howardjr_at_dizwell.com> writes
>>>Jim Smith wrote:
>>>
>>>> In message <4164e5de$0$10351$afc38c87_at_news.optusnet.com.au>, Howard J.
>>>> Rogers <howardjr_at_dizwell.com> writes
>>>>>xtanto wrote:
>>>>>
>>>>>> Hi Gurus,
>>>>>>
>>>>>> I have Table A with PK on a,b,c,d (so I have index on this)
>>>>>> Table A has FK on (a,b) to Table B)
>>>>>>
>>>>>> Table B with PK on a,b
>>>>>>
>>>>>> I have questions :
>>>>>>
>>>>>> (1) On Table A, what queries that can make use of the Index ?
>>>>>>
>>>>>> (2) On Table A, Do I need Index on (a,b) for the FK
>>>>>>
>>>>>> Thanks for your help,
>>>>>> xtanto
>>>>>
>>>>>
>>>>>This is all a bit abstract, isn't it?
>>>>>
>>>>>I'm sure someone here is going to ask when you have to have this
>>>>>homework in by...
>>>>>
>>>>>Besides which, although you might not think it, but the advice in
>>>>>respect of both questions is influenced by Oracle version. But that
>>>>>said:
>>>>>
>>>>>1. Any query. In 9i more than in 8i. Read up on skip scanning, for
>>>>>example. 2. Yes. Even in 9i, if you ever intend to modify table B's
>>>>>primary key.
>>>>>
>>>>
>>>> Does the fact that (a,b) is the leading portion of the primary key have
>>>> not any effect on this?
>>>
>>>
>>>Can we try being a little more precise? Have an effect on what?
>>>
>> Sorry. Should have snipped a bit.
>>
>> On the need for an index on the FK to avoid the table locking problem if
>> you change the PK.
>
>Nope. I'm still lost, and snipping's not going to help. Precision might.
>
>The guy says Table B has a primary key (a,b). You ask about (a,b) being the
>"leading portion" of the primary key. (a,b) *is* the primary key.
>
>If you are asking whether updating 2 columns of a 3 column primary key
>avoids the locking issue (or 1 of a 2 etc), no it doesn't.

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

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.

-- 
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>