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

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.

>Regards
>HJR

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