Sample Data Description Language › Indexes

Indexes

An index is a way to order a table logically to speed the retrieval of data.

For example, an index could be defined to order the EMPLOYEE table by employee last name. Another index could be established to order the same table by social security number.

An index is established on a column or combination of columns:

• To improve processing efficiency
• To prevent duplicate rows (when an index is specified as unique)

Identifying an Index

The system administrator examines all the columns and the programs that will run against the table to determine how the data is most likely to be accessed.

For example, assume several programs access the EMPLOYEE table to list employees alphabetically by last name. The system administrator would place an index on the employee last name to allow these programs to access the data efficiently.

There can be several indexes associated with one table.

When you issue a SELECT statement, SQL uses the indexes to access the data as efficiently as possible.

Creating an Index

To create an index on employee last name, the system administrator uses the following DDL statement:

create index lname_index
       on employee (emp_lname);

Indexes can be added or dropped as necessary.