Discussion Post on Performance

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Posts: 18
Joined: Mon Apr 05, 2010 11:11 am

Discussion Post on Performance

Postby gnkdev » Sat Feb 04, 2012 11:52 am

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)
*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

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Discussion Post on Performance

Postby Tim... » Sat Feb 04, 2012 1:40 pm


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
OakTable Member
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

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 6 guests