Does foreign key help in query performence?
WeesMay 29 2009 — edited Jun 3 2009Hi All,
I have two tables.
Dept
Dept_no(primary key) Name
-------------------------------------------------
10 Sales
20 Marketing
30 Production
Employee
Empno Name Dept(foreign key(dept_no)
--------------------------------------------------------------------
1 Emp1 10
2 Emp2 10
3 Emp3 20
4 Emp4 20
5 Emp5 30
If i run the below query
select * from dept a, employee b where b.dept=a.dept_no and a.dept_no=30;
While fetching from the dept table the query will use the primary key index to locate the dept no 30 but a full table scan will be done to fetch the information from employee table. Am I correct?