Databases Reference
In-Depth Information
Unlike primary key and unique key constraints, Oracle doesn't automatically create indexes on
foreign key columns. Therefore, you must manually create a foreign key index based on the columns
defined as the foreign key constraint. In most scenarios, you should create indexes on columns
associated with a foreign key. Here are two good reasons:
•
Oracle can often make use of an index on foreign key columns to improve the
performance of queries that join a parent table and child table (using the foreign
key columns).
If no B-tree index exists on the foreign key columns, when you insert or delete
from a child table, it locks all rows in the parent table. For applications that
actively modify both parent and child tables, this will cause locking and deadlock
issues
We'll first cover creating a B-tree index on a foreign key column and then show you some
techniques for detecting un-indexed foreign key columns.
•
Implementing an Index on a Foreign Key Column
Say you have a requirement that every record in the
ADDRESS
table be assigned a corresponding
CUST_ID
column that exists in the
CUST
table. To enforce this relationship, you create a foreign key constraint on
the
ADDRESS
table as follows:
alter table address add constraint addr_fk1
foreign key (cust_id) references cust(cust_id);
Note
A foreign key column must reference a column in the parent table that has a primary key or unique key
constraint defined on it. Otherwise you'll receive the error “ORA-02270: no matching unique or primary key for this
column-list.”
You realize the foreign key column is used extensively when joining the
CUST
and
ADDRESS
tables and
that an index on the foreign key column will increase performance. You have to manually create an
index in this situation. For example, a regular B-tree index is created on the foreign key column of
CUST_ID
in the
ADDRESS
table.
create index addr_fk1
on address(cust_id);
You don't have to name the index the same as the foreign key name (as we did in these lines of code). It's
a personal preference as to whether you do that. We feel it's easier to maintain environments when the
constraint and corresponding index have the same name.
When creating an index, if you don't specify the tablespace name, Oracle places the index in the
user's default tablespace. It's usually a good idea to explicitly specify which tablespace the index should
be placed in.
