Discussion Post on Performance

[gnkdev]

Is that Having Unindexed foreign keys can be a performance issue.

The fact that a table lock will result
*if you update the parent records primary key (very very unusual)
Or
*if you delete the parent record and the child's foreign key is not indexed.

Scenario: First session child table insertion without commit and on another session updating the parent table with primary column will cause performance Issue.

Sol1: Creating Index for the referential columns
Or
Sol2: Avoiding to update or delete Primary Columns

Which One is best Solution... ? or Any other Solution ?

Thanks & Regards,
Navin Kumar G

[Tim...]

Hi.

As always with Oracle, the answer is "it depends".

1) You should always question the logic of updating any indexed or constrained column unless it is absolutely necessary. Why? because you will force a lot of extra work that is unnecessary. You should only update a key column if the value has actually changed. This is a flaw in the "update the whole row all the time" approach that is common to many applications using "popular" frameworks.

2) Foreign keys are usually used in join conditions when querying the database. With this in mind, why would you consider leaving an FK column unindexed?

3) If the number of updates that could cause this issue are sufficiently small, and not in vital functional areas, do you care about a little extra locking?

4) In 9i the impact of this locking was drastically reduced. See: http://www.oracle-base.com/articles/9i/ ... indexedFKs

Cheers

Tim...