One of the simplest and often overlooked act when designing a data model in Oracle is omitting indexes on foreign keys. While I am not a big fan of implementing RI (Relation Integrity) within the database, it still remains a fact that due to the simplicity of enabling RI in the database, most shops often do it rather than within the code.
So in the simplest case when one has a need for a master-detail kind of relationships between two tables (like say Orders & Order_Lines) and there is a need to ensure spurious data does not get into you tables, one often uses the Foreign Key to ensure there are no child records in a details table without there being record in the parent table.
Now, when it comes to issuing a delete of a record in the parent table that involves deleting the primary key of the parent, then Oracle has to make sure associated records in the child table is also deleted respectively. Therefore to enforce this rule most Foreign Keys are enforced using "ON DELETE CASCADE", so when a parent record gets wiped out the associated child records are wiped out automatically by Oracle as well. And this is exactly where the issue of locking and enq: TM - contention crops. If the foreign key defined on the child table does not have an index created then, in the act of deleting a record in the parent, Oracle will LOCK the ENTIRE TABLE and no transactions can happen on the child table until the transaction that deleted from the parent commits or rollback.
There are a number of scripts on the Internet that you can Google to check absence of indexes on foreign keys. It is also the case that if parent records are rarely deleted then you really don't need indexes on the foreign key, so use your judgement.
Comments