In addition to the table lock issue that might hit you, an unindexed foreign key
is bad in the following cases as well:
-> When you have an on delete cascade and have not indexed the child table.
For example EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If
deptno in emp is not indexed, you will get a full table scan of EMP. This full
scan is probably undesirable and if you delete many rows from the parent table,
the child table will be scanned once for each parent row deleted.
-> When you query from the PARENT to the CHILD.Consider the EMP, DEPT
example again.It is very common to query the EMP table in the context of a
deptno.If you frequently query:
select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;
to generate a report or something, you'll find not having the index in place
will slow down the queries.
|
