Databases Reference
In-Depth Information
Indexes on Foreign Key Columns
Oracle doesn't automatically create indexes on foreign key columns. We recommend that you do create
B-tree indexes on foreign key columns, one reason being that foreign key columns are often referenced
in
WHERE
clauses and therefore performance can be improved with these queries
When indexes exist on foreign key columns this also reduces locking issues. Namely, when inserting
or deleting from the child table, this will put a table level lock on the parent table, which will prevent
other processes from inserting or deleting from the parent table. In OLTP databases, this can be an issue
when you have multiple processes inserting and deleting data simultaneously from parent and child
tables. In data warehouse environments, it's less of an issue because data is loaded in a more systematic
manner (scheduled batch jobs) and data is typically not deleted.
Here's a simple example of creating a table that has a foreign key and then manually creating an
index:
create table address
(address_id number primary key
,cust_id number references cust(cust_id)
,address varchar2(1000)
);
create index address_fk1 on address(cust_id);
Other Suitable Columns
When choosing an index, keep in mind this basic rule: the indexing strategy should be based on the
columns you use when querying the table. You can create more than one index on a table and have an
index contain multiple columns. You will make better decisions if you first consider what types of
queries you execute on a table. If you've identified a poorly performing SQL query, also consider creating
indexes for the following columns:
•
Create indexes on columns used often as predicates in the
WHERE
clause; when
multiple columns from a table are used in the
WHERE
clause, consider using a
concatenated (multi-column) index.
Create a covering index on columns used in the
SELECT
clause.
•
Consider creating indexes on columns used in the
ORDER BY
,
GROUP BY
,
UNION
, or
DISTINCT
clauses.
Oracle allows you to create an index that contains more than one column. Multicolumn indexes are
known as concatenated indexes (sometimes referred to as composite indexes). These indexes are
especially effective when you often use multiple columns in the
WHERE
clause when accessing a table.
Concatenated indexes are oftentimes more efficient in this situation than creating separate single
column indexes.
Columns included in the
SELECT
and
WHERE
clauses are also potential candidates for indexes. Recall
that a covering index is one that includes all columns returned by the query. In this situation, Oracle can
use the index structure itself (and not the table) to satisfy the results of the query. Also, if the column
values are selective enough, Oracle can use an index on columns referenced in the
WHERE
clause to
improve query performance.
Also consider creating indexes on columns used in the
ORDER BY
,
GROUP BY
,
UNION
, or
DISTINCT
clauses. This may result in more efficient queries that frequently use these SQL constructs.
•
