In my experience, the number one cause of deadlocks is a foreign key not indexed (the second reason is that the bitmap index on the table is being updated concurrently). In the following two cases, Oracle adds a full table lock to the child table after modifying the parent table:1) If you update the primary key of the parent table (which is rare if you follow the principle of a relational database, that is, the primary key should be immutable), the child table is locked because there is no index on the foreign key. 2) If a row in the parent table is deleted, the entire child table is also locked (because there is no index on the foreign key). In addition to full table locks, non-indexed foreign keys can also cause problems in the following situations: 1) If you have on DELETE CASCADE, and you do not index the child table: 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 make 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) Query the child table from the parent table: Consider the emp/dept example again. Using Deptno to query the EMP table is quite common. If you run the following query frequently (for example, generate a report), you will find that no index slows down the query:?? SELECT * FROM dept, EMP?? where Emp.deptno = Dept.deptno and Dept.deptno =: X; So, when do not need to foreign key index it? The answer is that, in general, no index is required when the following conditions are met: 1) No rows are deleted from the parent table. 2) The unique key/primary key value of the parent table is not updated (beware of tools sometimes inadvertently updating the primary key!) ）。 3) No child tables are joined from the parent table (such as Dept Junction to EMP). Reprint: http://blog.itpub.net/24104518/viewspace-716784/
Improve performance by establishing indexes on Oracle child table foreign keys
