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.
Regards
HJR
Received on Thu Oct 07 2004 - 06:09:44 CDT
