The fact that a table lock will result
*if you update the parent records primary key (very very unusual)
*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
Sol2: Avoiding to update or delete Primary Columns
Which One is best Solution... ? or Any other Solution ?
Thanks & Regards,
Navin Kumar G
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: https://oracle-base.com/articles/9i/ ... indexedFKs
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 6 guests