Command Discussion

• Each index is assigned a name and is related to a particular table based on the ON table-name clause.

• The entries in an index are ordered on a specified column within the specified table, and arranged in either ascending or descending order.

• The term index key refers to the column (or set of columns) in a table that is used to determine the order of entries in an index. Where an index does consist of multiple columns, the most important column is placed first.

• Further options controlling how and what type of index is created may be set in the WITH clause; see here.

• A table can have many different indexes associated with its columns. Each index is created by issuing a separate CREATE INDEX command.

Example: Many queries to the EMPLOYEE table reference the employee's department; therefore, the database designer decides to create an index using the DEPT column to improve performance related to these queries.

This command:

CREATE INDEX DEPTINDEX
     ON EMPLOYEE (DEPT) ;

would direct the creation of an index called DEPTINDEX (the Index name) on the table EMPLOYEE, using the DEPT column as indicated in parentheses as the Index key.

The index can be used by the system to provide quick access to employee data that are subject to conditions related to the employee's department.