DBA Tips Archive for Oracle

  


Creating Indexes on Foreign Keys

by Jeff Hunter, Sr. Database Administrator

Problem

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.

Solution

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-2017 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 jhunter@idevelopment.info.

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.

Last modified on
Friday, 21-Mar-2003 00:00:00 EST
Page Count: 58683