OraFAQ Forum: Server Administration » Index on foreign key

Home » RDBMS Server » Server Administration » Index on foreign key

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Index on foreign key [message #52926]

Wed, 21 August 2002 00:08

Nicolette Verdugt
Messages: 2
Registered: August 2002

Junior Member

Is it necessary to have an index on every foreign key?
I am reviewing a very complex database with many relations. The DBA made an index on every foreign key and this results in some tables with more than 10 indexes. I think this is bad for performance on database manipulations.
What to do?

Report message to a moderator

Re: Index on foreign key [message #52938 is a reply to message #52926]

Wed, 21 August 2002 05:10

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

it depends.
But i would recomend to create indexes on both primary
and foregin key, so as to avoid DEADLOCKS and prevent
an ORA-00060: deadlock detected while waiting for resource

Report message to a moderator

Re: Index on foreign key [message #52961 is a reply to message #52938]

Wed, 21 August 2002 14:56

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Well it won't necessarily give you deadlock errors. The reason that you need them is that if you lock a row in a parent table (select... for update OR update OR delete a row) then the whole child table will get locked if there is a FK constraint between parent and child. That will mean that a second user won't be able to get a row lock at the parent table level until the first user has commit/rollback.

If this doesn't apply to you then it's probably not an issue. The FK doesn't have to have it's own index. It can use leading columns of other indexes already existing on the table.

Report message to a moderator

Previous Topic:	Alternate for Global temporary tables
Next Topic:	Error 997 ....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 10:29:52 CST 2024