index on foreign key

[sandeepagarwal]

I have a table with columns A and B as Primary Key and about 2 million records. There is one child table with columns A,B and C as primary key and A,B as a foreign key to above table. This child table got 4 million records.

Do I need to create a index on columns A and B in child table for better performance? (Database: Oracle 8.1.7)

Sandeep.

 

[karluk]

If I understand you correctly, you most likely don't need an index on your foreign key. You already have an index on columns a,b,c because of your primary key. This index can be used very efficiently on almost every type of access you will do using the foreign key on a,b. I wouldn't generate the extra overhead of a second index on the same columns. The benefit you will gain just isn't worth the cost.

 

[sandeepagarwal]

Thanks... but if I don't create a separate index on the foreign key, does it not lock the parent table for every update in this child table?? I'm not sure how Oracle handles updation of child table for some key value in parent table.

Sandeep.

 

[karluk]

No. An index on a foreign key is normally useful in a variety of situations, for example when deleting rows from the parent table. Oracle finds an index extremely handy in this situation because it can easily verify whether the delete would result in orphans in the child table.

The point is that, if you already have an index on a,b,c in the child table, Oracle can just as easily verify the delete using this index. There's no need to create a second index on a,b.

 

[sandeepagarwal]

Thanks Karluk. I got it.

Sandeep.