Creating index on foreign key column?

Question

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

Department
    id
    dept_name
    ...

Employee
   id
   emp_name
   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 emp.dept_id=dept.id and dept.id=10;

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.

Answer 1

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: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754