Creating index on foreign key column

foreign keyindexoracle

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.

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

Related Solutions

Table design for departments with duplicate department numbers

The PK of your department table is not dept_id. The column dept_id is an attribute of a manager table that you have not defined, since it is functionally dependent on the manager_id.

The PK of your department table is dept_name.

Primary keys don't have to be integers.

Note: If your HR department creates departments with the same name, that is going to land you back in hot water. You might need to create a composite primary key of dept_id and dept_name. This could still lead to trouble if your HR department can't be trained to keep department names unique within managers. Excel is not a good data source for being able to enforce constraints.

Postgresql – Insertion of data violates the foreign key constraint

In Postgres, there are two ways to circumvent the chicken-and-egg problem of inserting into tables with circular references.

a) Defer one of the constraints, either declaring it as DEFERRABLE INITIALLY DEFERRED when created or as DEFERRABLE as created and then defer it when needed.

For an example of using deferrable constraints, see my answer in this question:
Implementation of a many-to-many relationship with total participation constraints in SQL.

b) Use a single statement to insert into the two (or more) tables, using modifying CTEs. Example for your case:

with
  insert_emp as
  ( insert into employee 
        (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
    values
        ('James', 'E', 'Borg', '888665555', '1937-11-10', 'asda', 'M', 55000, NULL, 1)
    returning 
        *
  ), 
  insert_dep
  ( insert into sp0090.department 
        (Dname, Dnumber, Mgr_ssn, Mgr_start_date) 
    values 
        ('Headquarter', 1, '888665555', '1981-06-19')
    returning 
        *
  ) 
select *
from insert_emp as e
  full join insert_dep as d
  on  e.dno = d.dnumber
  and e.ssn = d.mgr_ssn ;

Best Answer

Related Solutions

Table design for departments with duplicate department numbers

Postgresql – Insertion of data violates the foreign key constraint

Related Question