Databases Reference
In-Depth Information
Note
Domain indexes, B-tree cluster indexes, and hash clusters are not covered extensively in this topic. If you
need more information regarding these index types, see Oracle's SQL Reference Guide at
Determining Which Columns to Index
Now we turn our attention to figuring out which columns should be indexed. For starters, we
recommend for most applications you create indexes in the following situations:
•
Define a primary key constraint for each table
: This results in an index
automatically being created on the columns specified in the primary key.
•
Create unique key constraints on columns that are required to be unique and are
different from the primary key columns
: Each unique key constraint results in an
index automatically being created on the columns specified in the constraint.
Manually create indexes on foreign key columns
: This is done for better
performance to avoid certain locking issues (see Chapter 2 for complete details).
Each of these bulleted items is detailed in the following subsections.
•
Tip
See Chapter 9 for obtaining indexing advice from the SQL Tuning Advisor. See Chapter 10 for generating
indexing recommendations from the SQL Access Advisor.
Indexes on Primary Key and Unique Key Columns
In most situations, you should create a primary key constraint for every table. If there is not already an
index defined on the primary key columns, then Oracle will automatically create a B-tree index for you.
Similarly, for any unique key constraints you define on a table, if there is not already an index
defined on the unique key columns, Oracle will create an appropriate B-tree index. Here's a simple
example:
create table cust
(cust_id number primary key
,last_name varchar2(30)
,first_name varchar2(30)
,ssn varchar2(16) unique);
See Chapter 2 for complete details regarding primary key and unique key constraints and how they
related to indexes.
