Importance of Indexing Foreign Key Columns -

Introduction

In a relational database, defining constraints can help ensure data integrity, improve data consistency, enhance SQL query performance, and, in the case of foreign keys, ensure referential integrity. I talk a lot more about constraints in a previous post about database constraints. In a separate post, I also talk about locks and transaction concurrency with some tips for preventing blocking sessions.

These blocking sessions, caused by a session holding a lock on a resource, cause other sessions to wait, sometimes indefinitely. In some cases, unnecessary blocking can be caused by missing indexes on foreign key columns. When this is the case, Oracle will place a table lock on the child table when an UPDATE or DELETE of the parent table’s primary key column is issued. This can result in locking that blocks other sessions.

When Oracle cannot acquire this lock (perhaps due to some uncommitted DML on the child table), the session updating the parent table would be blocked.

In this post, I explore an example scenario.

Demonstration

Setup

In order to demonstrate this, we create 2 tables ; emp and dept. The dept and emp table have a parent-child relationship enforced using foreign key constraints. We can create the tables and insert some data using the code below :

PS: I am using Oracle Database 23.26.2.0.0 for these examples.

drop table if exists emp ;
drop table if exists dept ;

CREATE TABLE dept
  ( dept_id   NUMBER PRIMARY KEY
   ,dept_name VARCHAR2(32 CHAR) 
 );

CREATE TABLE emp
 ( emp_id   NUMBER PRIMARY KEY
  ,emp_name VARCHAR2(64 CHAR)
  ,salary   NUMBER
  ,dept_id  NUMBER REFERENCES dept(dept_id)
 );

INSERT INTO dept 
  VALUES  (1,'IT')
         ,(2,'HR')
         ,(3,'SALES')
         ,(4,'FINANCE') 
         ,(5,'ANALYSTS') ;

INSERT INTO emp (emp_id, emp_name, salary, dept_id)
VALUES
       ( 1, 'William Smith'  , 7400 , 4)
      ,( 2, 'Elizabeth Bates', 7300 , 1)
      ,( 3, 'Sundita Kumar'  , 6100 , 4)
      ,( 4, 'Ellen Abel'     , 11000, 2)
      ,( 5, 'Alyssa Hutton'  , 8800 , 1)
      ,( 6, 'Jonathon Taylor', 8600 , 3)
      ,( 7, 'Jack Livingston', 8400 , 3)
      ,( 8, 'Kimberely Grant', 7000 , 1)
      ,( 9, 'Charles Johnson', 6200 , 4)
      ,(10, 'Winston Taylor' , 3200 , 2)
;

COMMIT;

Notice the use of the IF[NOT] EXISTS and VALUES clauses.

Our foreign key column emp.dept_id, which references dept.dept_id, is not indexed. This can lead to blocking when updating rows in the parent dept table because Oracle must verify whether child rows exist in emp that reference the value being modified.

Indefinite Waits

This can be demonstrated using 2 sessions. The first session updates the foreign key value for a single row in the emp table. The second session attempts to modify a parent key in the dept table for a department that is not currently referenced by any employee.

From the first session:

update emp
set    dept_id = 3
where  emp_id = 1 ;

1 row updated.

From the second session :

update dept
set   dept_id = 6
where dept_id = 5 ;

Pending the first session’s commit, the second session sits there waiting. If the transaction is left open for example, because an application becomes unresponsive or a user abandons the session the wait can continue indefinitely.

From a third session we can display blocking details in the database, using the script sel_blocking_and_blocked_sessions.sql.

SELECT
       bs.sid              AS blocker_sid,
       bs.serial#          AS blocker_serial,
       bs.username         AS blocker_user,
       bs.status           AS blocker_status,
       ws.sid              AS blocked_sid,
       ws.serial#          AS blocked_serial,
       ws.username         AS blocked_user,
       ws.event            AS blocked_event,
       ws.seconds_in_wait
FROM
         v$session ws
    JOIN v$session bs  ON ws.blocking_session = bs.sid
ORDER BY
         ws.seconds_in_wait DESC
/

BLOCKER_SID BLOCKER_SERIAL BLOCKER_USER         BLOCKER_STATUS       BLOCKED_SID BLOCKED_SERIAL BLOCKED_USER         BLOCKED_EVENT                       SECONDS_IN_WAIT
----------- -------------- -------------------- -------------------- ----------- -------------- -------------------- ----------------------------------- ---------------
         25           4612 HR                   INACTIVE                     300          32948 HR                   enq: TX - row lock contention                    72

As seen above, there is an enq: TX - row lock contention as the second session cannot complete its update while the first session’s transaction is still active. This is because while updating the primary key column in the dept table, Oracle must ensure that any dept_id values in EMP continue to reference valid rows in DEPT. It must also ensure that concurrent changes do not leave the parent-child relationship in an inconsistent state. Without an index on emp.dept_id, Oracle cannot quickly find the child rows that reference the parent row.

If we commit or rollback from the first session, the lock clears and the second session’s update processes.

Unless a COMMIT or ROLLBACK is issued, the second session stays waiting. In situations where :

that update is the first in a series of steps for a transaction,
an application session gets stuck
or someone leaves the transaction open and goes home

Then our second session could be stuck waiting … well indefinitely!

Prevent the Blocking

If we index the dept_id column in the emp table, this specific blocking scenario can be prevented.

CREATE INDEX emp_dept_id_ix 
ON emp(dept_id) ;

Index EMP_DEPT_ID_IX created.

We run both updates from 2 different sessions and notice no blocking.

The Index Type Matters

In the example above, I used the default B-tree index to prevent the block from occurring. If I used a Bitmap index, the issue would not be solved. With bitmap indexes, a change to one row can require updates to shared bitmap structures, which can result in more locking and blocking between concurrent DML operations.

We can demonstrate this by dropping the index, creating a bitmap index, and attempting the update again.

DROP INDEX emp_dept_id_ix ;

...

CREATE BITMAP INDEX  emp_dept_id_ix 
ON emp(dept_id) ;

From 1 session

UPDATE EMP
SET    dept_id = 3
WHERE  emp_id = 1 ;

1 row updated.

and from another

update dept
set   dept_id = 6
where dept_id = 5 ;

The locking is still observed

Bitmap indexes are best suited for reporting and Data Warehouse environments where data changes infrequently and complex queries benefit from bitmap operations. In OLTP environments, where many users perform concurrent inserts, updates, and deletes, B-tree indexes are typically the appropriate choice because they provide much better concurrency characteristics.

Conclusion

In this post, we explored what could happen if an index is not defined on a foreign key column and how this could lead to blocking sessions. Although Oracle does not require indexes on foreign key columns for correctness, adding B-tree indexes to frequently referenced foreign keys is considered a best practice in OLTP systems. As well, the index type used is important. In a concurrent database with multiple users, you want to ensure that blocking sessions are avoided as much as possible, and considering this brings you closer to that objective.

Hopefully this is helpful,
Thanks for reading.

Importance of Indexing Foreign Key Columns