Use of Indexes in Foreign Key

Normally we used to avoid creating indexes in the foreign keys, with unindexed foreign

key columns; Oracle will lock the child table

WHEN TO INDEX:

In addition to the table lock issue that might hit you, an unindexed foreign key
is bad in the following cases as well:

à When you have an on delete cascade and have not indexed the child table. 

   For example EMP is child of DEPT. 

   Delete deptno = 10 should cascade to EMP.

   If deptno in emp is not indexed, you will get a full table scan of EMP.
 This full scan is probably 

   undesirable and if you delete many rows from the parent table,
 the child table will be scanned once for each parent row deleted.

à When you query from the PARENT to the CHILD.  

  Consider the EMP, DEPT example again. It is very common to query the EMP table
in the context of a deptno. 

  If you frequently query:

     select * from dept, emp

     where emp.deptno = dept.deptno and dept.deptno = :X;

 to generate a report or something, you'll find not having the index in place
will slow down the queries.

WHEN NOT TO INDEX:

So, when do you NOT need to index a foreign key?  In general when the following
conditions are met:

à You do NOT delete from the parent table. (Especially with delete cascade)

à You do NOT update the parent table’s unique/primary key value.

à You do NOT join from the PARENT to the CHILD (like DEPT->EMP).

If you satisfy all three above, feel free to skip the index, it is not needed.
If you do any of the above, be aware of the consequences.

As for the effect of an index on a foreign key index on an OLTP system
-- it depends.

If you not update the foreign key the overhead is during the insert
and might not be noticed.  If you update it frequently, it might be worse

We will go through the following example which is having the delete statement which deletes from the parent table which is having a reference with the child table.

Example without index on child table

-----------------------------------------------------------------------------

-- Create a Parent Table.

CREATE TABLE PARENT(NO NUMBER);

-- Insert some sample data in to the Parent table.

INSERT INTO PARENT(NO) SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 10001;

COMMIT;

-- Add Primary Key Constraint to it.

ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (NO);

-- Create a Child table.

CREATE TABLE CHILD(NO NUMBER,NAME VARCHAR2(10));

-- Insert some sample records wit the value from the parent table

INSERT INTO CHILD(NO,NAME)

SELECT A.NO,A.NAME FROM

(SELECT NO,'RAFEEK' AS NAME FROM PARENT WHERE ROWNUM < 10000)A, (SELECT 1 FROM DUAL

UNION ALL

SELECT 2 FROM DUAL);

-- Commit the transaction

COMMIT;

-- Add foreign key constraint to it.

ALTER TABLE CHILD ADD CONSTRAINT REFPARENT FOREIGN KEY (NO) REFERENCES PARENT (NO);

-- Analyze both the tables (Gather Statistics)

ANALYZE TABLE CHILD COMPUTE STATISTICS;

ANALYZE TABLE PARENT COMPUTE STATISTICS;

--NOW WE WILL CHECK THE PERFORMANCE PROBLEM

-- delete All the records from the child table

DELETE FROM CHILD;

COMMIT;

ANALYZE TABLE CHILD COMPUTE STATISTICS;

--TO CAPTURE THE TIMING

SET TIMING ON

--DELET FROM THE PARENT TABLE

DELETE FROM PARENT;

COMMIT;

SET TIMING OFF

--NOTE DOWN THE TIME TAKEN

---------------------------------------------------------------------------

Example with Index on child table

DROP TABLE CHILD;

DROP TABLE PARENT;

CREATE TABLE PARENT (NO NUMBER);

INSERT INTO PARENT (NO) SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 50001;

COMMIT;

ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (NO);

CREATE TABLE CHILD (NO NUMBER, NAME VARCHAR2 (10));

INSERT INTO CHILD (NO, NAME)

SELECT A.NO, A.NAME FROM

(SELECT NO,'RAFEEK' AS NAME FROM PARENT WHERE ROWNUM < 10000)A, (SELECT 1 FROM DUAL

UNION ALL

SELECT 2 FROM DUAL);

COMMIT;

ALTER TABLE CHILD ADD CONSTRAINT REFPARENT FOREIGN KEY (NO) REFERENCES PARENT (NO);

CREATE INDEX CHILD1_IX ON CHILD (NO);

ANALYZE TABLE CHILD COMPUTE STATISTICS;

ANALYZE TABLE PARENT COMPUTE STATISTICS;

ANALYZE INDEX CHILD1_IX COMPUTE STATISTICS;

DELETE FROM CHILD;

COMMIT;

ANALYZE TABLE CHILD COMPUTE STATISTICS;

SET TIMING ON

DELETE FROM PARENT;

COMMIT;

SET TIMING OFF

--NOTE DOWN THE TIME TAKEN

Thanks & Regards,

Mohamed Rafeek, S