Creating Indexes

The purpose of indexes can be summarized in one word: performance. An index is a database structure designed to reduce the amount of time necessary to retrieve one or more rows from a table. Indexes can also enforce uniqueness on one or more columns of a table.

index

A database object designed to reduce the amount of time it takes to retrieve rows from a table. An index is created based on one or more columns in the table.

Any number of indexes may be created on a table. An index may also be built against a combination of columns in a table; this type of index is known as a composite index.

composite index

An index that is created on two or more columns in a table.

Indexes are maintained automatically. When new rows are added to the table, new entries are recorded in the indexes. When rows are deleted from the table, the corresponding index entries are also deleted.

Warning

Be cautious when creating indexes in an environment with frequent update, insert, and delete operations. The overhead of keeping the indexes up to date can have a performance impact on the database and potentially increase the response time for users.

Indexes can be either unique or nonunique. A unique index prevents duplicate values from being inserted into a table column with a unique index. For example, an employee table might have a column with a social security number. Since no two employees will have the same social security number, a unique index can be created on the column. If a primary key is defined for a table, a unique index is automatically created to enforce the uniqueness of the primary key.

Nonunique indexes, by definition, will not enforce uniqueness, but can still speed processing by narrowing down the range of blocks where the desired rows of a table can be found. For example, a nonunique index on a column with a last name would likely have many entries for Smith. Each of the index entries for Smith would point to a row in the table where the last name was Smith. Using this nonunique index to find all the Smith entries will typically take much less time than scanning the entire table for Smith directly.

An index on a database table column corresponds closely to the real-world analogy of an index in a book. A topic in a book can be located much more quickly if the topic’s title is located in the book’s index with the corresponding page number. Without the index, you might need to search through each page of the book to locate the topic you want.

The simplest form of the CREATE INDEX statement looks like this:

CREATE INDEX index_name ON table_name (column1[, column2]...);

The columns column1, column2, and so forth are the columns to be indexed on the table table_name. The index name index_name must be unique across all objects within the same schema.

Janice has been receiving complaints that the queries against the COUNTRIES table have been slow. She knows that there is already an index on the COUNTRY_ID column, so she is surprised that the response time would be poor when selecting a row from the COUNTRIES table. After further investigation, she discovers that a lot of users are trying to find the two-letter country code given the name of the country—the users are searching the table using a WHERE clause on the COUNTRY_NAME column. She decides that an index on the COUNTRY_NAME column might improve the response time. To create the index, she uses the following command:

create index countries_ie1 on countries(country_name); Index created.

The index did not necessarily need the name of the table in its name. However, Janice realizes that it’s good practice to include the table name, so that she can easily avoid duplicate index names in the database.