Oracle foreign keys and Locks

In Oracle, if the foreign key is not indexed, the modification to the parent table causes the child table to be added to the full table lock. This consists of two cases:

1. Delete rows from the parent table, if there is no index on the foreign key, causes the child table to be added a full table lock

2. Update the primary key of the parent table (according to the principle of the relational database, updating the primary key is a huge "taboo", so this is generally not the case), if there is no index on the foreign key, it causes the child table to be added full table lock

Although, in oracle9i and above, these full-table locks are short-lived, they exist only during DML operations, not during the entire transaction. But even so, these full-table locks are likely (and do) cause serious locking problems.

You can query for the presence of unindexed foreign keys by using the following statement:

SELECTtable_name, constraint_name, CNAME1||NVL2 (CNAME2,', ' ||CNAME2,NULL)||NVL2 (CNAME3,', ' ||CNAME3,NULL)||NVL2 (CNAME4,', ' ||CNAME4,NULL)||NVL2 (CNAME5,', ' ||CNAME5,NULL)||NVL2 (CNAME6,', ' ||CNAME6,NULL)||NVL2 (CNAME7,', ' ||CNAME7,NULL)||NVL2 (CNAME8,', ' ||CNAME8,NULL) COLUMNS from(SELECTB.table_name, B.constraint_name,MAX(DECODE (POSITION,1, COLUMN_NAME,NULL)) CNAME1,MAX(DECODE (POSITION,2, COLUMN_NAME,NULL)) CNAME2,MAX(DECODE (POSITION,3, COLUMN_NAME,NULL)) CNAME3,MAX(DECODE (POSITION,4, COLUMN_NAME,NULL)) CNAME4,MAX(DECODE (POSITION,5, COLUMN_NAME,NULL)) CNAME5,MAX(DECODE (POSITION,6, COLUMN_NAME,NULL)) CNAME6,MAX(DECODE (POSITION,7, COLUMN_NAME,NULL)) CNAME7,MAX(DECODE (POSITION,8, COLUMN_NAME,NULL)) CNAME8,COUNT(*) col_cnt from(SELECTSUBSTR (TABLE_NAME,1, -) table_name, SUBSTR (constraint_name,1, -) constraint_name, SUBSTR (column_name,1, -) column_name, POSITION fromuser_cons_columns) A, user_constraints BWHEREA.constraint_name=B.constraint_name andB.constraint_type= 'R'         GROUP  byB.table_name, B.constraint_name) CONSWHERECol_cnt>  All(SELECT COUNT(*)           fromuser_ind_columns IWHEREI.table_name=CONS. TABLE_NAME andI.column_nameinch(CNAME1, CNAME2, CNAME3,                                 CNAME4, CNAME5, CNAME6, CNAME7, CNAME8) andI.column_position<=CONS. Col_cntGROUP  byI.index_name)

This script will handle up to 8 columns of FOREIGN KEY constraints (if your foreign key has more columns, you may have to reconsider your design)!

In addition to full table locks, non-indexed foreign keys can also cause problems in the following situations:
1. If there is an on DELETE cascade, and No child indexes are added: For example, the EMP is a Dept child table, delete deptno=10 should cascade (CASCADE) to EMP. If the DEPTNO in the EMP does not have an index, then deleting each row in the Dept table will do a full table scan of the EMP. This full-table scan may not be necessary, and if multiple rows are deleted from the parent table, the child table is scanned once per row in the parent table.

2. Querying a child table from a parent table: Consider the emp/dept example here. Using Deptno to query the EMP table is quite common. If you run the following query frequently, you will find that no index slows down the query:

Select *      from Dept, EMP     where emp.deptno=dept.deptno       and Dept.deptno=: x;

Therefore, it is important to note whether foreign key indexes are required to prevent such Oracle from "excessively locking" the data.

--refer to advanced programming from Oracle Experts

Oracle foreign keys and Locks