Re: sql question

Since 7.1.6 it's only limited DML activity on the parent that will table lock, if the index is in place. Since most accesses on e.g. reference tables are from the child (using, say, the primary key or a significant column), an index on the foreign key is often of little or no value apart from preventing table locking. If the parent table is only rarely modified or is managed in an organised way (say, out of hours or at periods of low activity), the rare occasions when table locking will occur will be of minimal effect.

OTOH, too many indexes on a table will cause significant performance hits to updates, inserts and deletes. For many tables, indexing all foreign keys could slow the system considerably. (I guess if all your tables only have one or two foreign key relationships, the point is academic.)

As always, the decision whether to index any column is a design issue and should be considered case by case. In fact, I start by assuming they should *not* be applied. The questions then are:

• do you ever select on the *child* table on the foreign key first?
• is the parent table a business table (rather than look-up/reference) with user-driven deletes?

In these and similar cases, an index may well be beneficial.

(IMHO) Len

>It means you can do DML on the parent table without the entire child table
>being locked for the duration of the transaction. In mho, I recommend ALL
>columns subject to a foregin key constraint should be indexed without even
>thinking about it, as a result.
>
>Regards
>HJR
>--
>--------------------------------------------------------------------------
>Opinions expressed are my own, and not those of Oracle Corporation
>Oracle DBA Resources: http://www.geocities.com/howardjr2000
>--------------------------------------------------------------------------
>
><volk333_at_my-deja.com> wrote in message news:8t5quf$i1n$1_at_nnrp1.deja.com...
>> what happens if an index is created on a foreign key column?
>> i met this question in some braindump for OCP.
>> someone can help me answer this?
>> thank you
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
>
  Received on Tue Oct 31 2000 - 16:09:30 CST