Question: I understand that enforcing
referential integrity is important in Oracle and foreign key
constraints are critical to maintaining parent child relationships.
However, I see discussion where they indicate that the rules for
indexing foreign keys of a child table have changed over the
releases of Oracle.
I am unclear why the rules for indexing a foreign key would
change because of the release of Oracle?
Answer: The
changes in Oracle best practices for indexing foreign keys relates
directly to Oracle's internal locking mechanism, specifically the
duration of the lock, which may cause contention during heavy DML
activity on the parent table.
Also see this script to
find foreign keys
with no indexes.
Even though Oracle allows you to define a foreign key without an
index, it's the SQL workload that determines whether an index is
required. A "missing" foreign key index will manifest itself
with unnecessary large-table full-table scans against the child
table.
As a general rule, the need to define a foreign key implies that
there will be SQL statements that require a join of the parent and
child tables. Without a foreign key index, this SQL could not
perform a nested loops join, and a more expensive sort
merge join might be required:
select
cust_name, order_details
from
cust c,
ordor o,
where
c.cust_id =
o.cust_id;
In general, an index is required on a foreign key when:
- You see boatloads of "TABLE ACCESS FULL' operations against
the child table when you perform DML against the parent table.
- You update/delete against the primary key in the parent
table
When you create a foreign key constraint, you can choose the "on
delete restrict" option to ensure that a parent rows cannot be deleted
while a child row still exists. You can also implement
on delete cascade to
delete all child
rows when a parent row is deleted.
Obviously, if you are using "on delete cascade" and you
do not create an index on the child parent key the deletion of a
parent row would require a full-table scan of the child table, to
find and delete the child rows. Likewise, and "on delete
restrict" without an index would force Oracle to perform a
full-table scan against the child table to see if any children
exist.
Locking and foreign key indexes
Without an index on the child table's foreign key, table-level
locking may occur when a parent row is updated. Rampant author
Jeff
Hunter notes that there is a table-level locking issue prior to
release 11g:
ALTER
TABLE emp
ADD
CONSTRAINT emp_fk1
FOREIGN KEY (deptno)
REFERENCES dept (deptno);
Once this constraint is enabled, attempting to insert an "EMP"
record with an invalid DEPTNO, or trying to delete a DEPTNO row that
has matching "EMP" records, will generate an error.
However,
in order to preserve integrity during the operation, Oracle needs to
apply a full "table-level" lock (as opposed to the usual row-level
locks) to the child table when the parent table is modified.
By creating an index on the foreign key of the
child table, these "table-level" locks can be avoided. (for
instance, creating a foreign key on "EMP.DEPTNO").
CREATE INDEX emp_n1
ON emp(deptno)
TABLESPACE indx;
Keep in mind that you will often be creating an index on
the foreign keys in order to optimize join and queries.
|However, if you fail to create such a foreign key index and if the
parent table is subject to updates, you may see heavy lock
contention. If ever in doubt, it's often safer to create indexes on
ALL foreign keys, despite the possible overhead of maintaining
unneeded indexes.
