| index on foreign keys [message #467101] |
Thu, 22 July 2010 04:47  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi,
I have to create indexes on foreign key columns ,now pl suggest me if composite index is already there with foreign key column then that will work or i will have to create a single column index.
Thanks
Prashant
|
|
|
|
|
|
|
|
|
|
| Re: index on foreign keys [message #467121 is a reply to message #467113] |
Thu, 22 July 2010 05:07  |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What's skip scans got to do with anything?
You need to index foreign key columns to avoid locking issues, and possible deadlocks, if you modify the parent.
For that to work the foreign key columns need to be on the leading edge of the index.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: index on foreign keys [message #467153 is a reply to message #467121] |
Thu, 22 July 2010 06:06 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
ok. actually main purpose of creating index on foreign key is to avoid the locking.now if i create all the indexes on foreign keys then more indexes on system will not hamper the performance?
|
|
|
|
| Re: index on foreign keys [message #467159 is a reply to message #467153] |
Thu, 22 July 2010 06:10 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well more indexes means more work to do an insert/update/delete but generally it shouldn't be noticeable. If you're worried about performance, test it.
Bear in mind that if the parent table never changes then you don't need to index foreign keys to it.
|
|
|
|
| Re: index on foreign keys [message #467177 is a reply to message #467159] |
Thu, 22 July 2010 08:07 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OP doesn't say what release he is using, but from release 9, you no longer have the table lock problem if the FK column is not indexed.
As I understand it, Oracle reversed the implementation of the FK check. Up to 8i, if a session did DML on a parent table (and the child table had no FK index) the session had to lock the entire child table to ensure that no conflicting transaction was started. But from 9i, the session doing the DML on the parent locks only the row in the parent table and all sessions doing DML on the child table do a look-up on the parent to see if the parent row is locked. When I realized this, I thought "Why didn't they do it that way from the beginning?"
|
|
|
|
|
|
|
|
| Re: index on foreign keys [message #467196 is a reply to message #467190] |
Thu, 22 July 2010 10:53 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Thanks, CM, that is interesting. I've just tested it, using the SCOTT schema on 11.2. It seems to me that the problem of locking the child table is indeed solved, in so far as DML against the child table will (from 9i on) proceed with no noticeable problems caused by executing DML on the parent. However, transactions against the child table can now block concurrent DML on the parent table.
But is it really a problem? Probably not. The case where you don't bother to index an FK column is usually where the column is checked against some sort of look-up table, used just for validation: not a true dimension table, used for predicates. So I think I would probably prefer not to have the overhead of maintaining an index, and accept the remote possibility of problems when doing work on the look-up table.
Interesting that the problem is now kind-of reversed!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
