Home » SQL & PL/SQL » SQL & PL/SQL » Index on composite foreign key (Oracle 11)
Index on composite foreign key [message #650617] Thu, 28 April 2016 06:56 Go to next message
RaShi.Raj
Messages: 84
Registered: May 2014
Location: Bangalore
Member
Hi,

I have a foreign key on multiple columns (col1, col2) and since I need to create an index on foreign keys to handle table lock, is it mandatory that index should also be in same order like index on (col1, col2) and not on (col2, col1)???

Thanks,
Rashi

Report message to a moderator

Re: Index on composite foreign key [message #650618 is a reply to message #650617] Thu, 28 April 2016 07:16 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
What lock are you concerned about, under what circumstances? THe reason ask is that you say you are using release 11.something, which behaves differently from earlier releases.

Report message to a moderator

Re: Index on composite foreign key [message #650619 is a reply to message #650617] Thu, 28 April 2016 07:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
index is NOT mandatory to enforce FK constraint; so column order is not required.
Can you prove me wrong with reproducible test case?

Report message to a moderator

Re: Index on composite foreign key [message #650620 is a reply to message #650618] Thu, 28 April 2016 07:25 Go to previous messageGo to next message
RaShi.Raj
Messages: 84
Registered: May 2014
Location: Bangalore
Member
John, It's parent table lock that occurs when it's modified as explained here -> http://www.dba-oracle.com/t_foreign_key_indexing.htm

Report message to a moderator

Re: Index on composite foreign key [message #650621 is a reply to message #650620] Thu, 28 April 2016 07:28 Go to previous messageGo to next message
RaShi.Raj
Messages: 84
Registered: May 2014
Location: Bangalore
Member
BlackSwan, it's not to enforce FK constraint but to avoid deadlocks as explained in the above link provide. We have faced such issue in our environment.

Report message to a moderator

Re: Index on composite foreign key [message #650622 is a reply to message #650621] Thu, 28 April 2016 07:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I concur.

Report message to a moderator

Re: Index on composite foreign key [message #650624 is a reply to message #650620] Thu, 28 April 2016 07:38 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
RaShi.Raj wrote on Thu, 28 April 2016 13:25
John, It's parent table lock that occurs when it's modified as explained here -> http://www.dba-oracle.com/t_foreign_key_indexing.htm

That article is describing the pre-11.x behaviour. You don't get locks like that now. Can you show the problem that you are facing? It isn't that one.

Report message to a moderator

Re: Index on composite foreign key [message #650626 is a reply to message #650620] Thu, 28 April 2016 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
RaShi.Raj wrote on Thu, 28 April 2016 14:25
John, It's parent table lock that occurs when it's modified as explained here ->...


This site is b.....t, forget it, for ever.

Report message to a moderator

Re: Index on composite foreign key [message #650632 is a reply to message #650626] Thu, 28 April 2016 08:58 Go to previous messageGo to next message
RaShi.Raj
Messages: 84
Registered: May 2014
Location: Bangalore
Member
This behavior is explained here -> https://perfstat.wordpress.com/2014/09/08/deadlocks-due-to-unindexed-foreign-keys/

I could able to recreate it on 11G...will try to see if order of columns makes any difference. Thanks

Report message to a moderator

Re: Index on composite foreign key [message #650635 is a reply to message #650632] Thu, 28 April 2016 09:59 Go to previous messageGo to next message
RaShi.Raj
Messages: 84
Registered: May 2014
Location: Bangalore
Member
Order of columns in composite foreign key and indexed columns need not be in same order...i.e. suppose we have Foreign Key on (col1, col2) and index can be on (col2, col1) or (col1, col2).

Report message to a moderator

Re: Index on composite foreign key [message #650636 is a reply to message #650635] Thu, 28 April 2016 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It does not matter for this point, it may for performances of your SQL statements.

Report message to a moderator

Re: Index on composite foreign key [message #650638 is a reply to message #650636] Thu, 28 April 2016 10:06 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I would always create an index in the same order as my foreign key. It will work without it, but anytime you delete from the parent, oracle would have to do a full table scan on the child table to find out if their are children or to do a cascade delete and depending on the number of rows, that can take a long time.

Report message to a moderator

Previous Topic: Hierarchical Query (merged)
Next Topic: Dynamic selection of data in query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 23:41:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.