DBA Tips Archive for Oracle
Creating Indexes on Foreign Keys
by Jeff Hunter, Sr. Database Administrator
Creating foreign keys constraints on tables increases the integrity of your data by preventing rows from being inserted into detail (sometimes called child tables) table that do not have a matching row in a master (also called the parent table) table.
The following code creates two tables: "EMP" and "DEPT". Both tables declare a primary key and the table "EMP" declares a foreign key constraint between "EMP" and "DEPT".
CREATE TABLE dept ( deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY , dname VARCHAR2(14) , loc VARCHAR2(13) ); CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY , ename VARCHAR2(10) , job VARCHAR2(9) , mgr NUMBER(4) , hiredate DATE , sal NUMBER(7,2) , comm NUMBER(7,2) , deptno NUMBER(2) ); ALTER TABLE emp ADD CONSTRAINT emp_fk1 FOREIGN KEY (deptno) REFERENCES dept (deptno);
Once this constraint is enabled, attempting to insert an "EMP" record with an invalid DEPTNO, or trying to delete a DEPTNO row that has matching "EMP" records, will generate an error. However, in order to preserve integrity during the operation, Oracle needs to apply a full "table-level" lock (as opposed to the usual row-level locks) to the child table when the parent table is modified.
By creating an index on the foreign key of the child table, these "table-level" locks can be avoided. (for instance, creating a foreign key on "EMP.DEPTNO").CREATE INDEX emp_n1 ON emp(deptno) TABLESPACE indx;
Keep in mind that you will often be creating an index on the foreign keys in order to optimize join and queries. However, if you fail to create such a foreign key index and if the parent table is subject to updates, you may see heavy lock contention. If ever in doubt, it's often safer to create indexes on ALL foreign keys, despite the possible overhead of maintaining unneeded indexes.
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.