Creating index on foreign key column

foreign keyindexoracle

I have below table structure where one department can have many employees.


   dept_id(foreign key to department table)

Consider I have 30 departments and each department has 10k records.

Now I fire below query

select * from department dept, Employee emp where and;

My question is there any benefit on creating index on foreign key column dept_id of table Employee ?

Per my understanding, there should be benefit of creating index on column dept_id as without index DBMS has to scan all records of Employee
table to find dept_id=10. If there is index then it can quickly find the range of record having dept_id=10 with the help of non clustered index.

Best Answer

It is highly recommended to create an index on Employee.dept_id.

It should make a significant improvement for such SELECT statements. Even more important the index becomes on updates. You may lock entire table (forcing other sessions to wait) until you finish your transaction.

See more information here: