Defining a Database Using SQL › Defining an Index

Defining an Index

You define an index by issuing the CREATE INDEX statement.

Things You Can Specify

1. Whether the index is unique
2. Name of the index
3. Name of the table on which the index is defined
4. Name of the column or columns that make up the index key
5. The sequencing options for the index
6. Optionally, the area in which the index will be stored
7. Optionally, physical characteristics of the index
8. Optionally, physical attributes, including an index ID

Considerations

• CA IDMS/DB will automatically determine the physical characteristics of the index based on the estimated (or actual) number of rows in the table. However, you may choose to supply this information yourself.
• Index names must be unique for all indexes defined on a table
• An index must be in the same page group as the table on which it is defined.

Specifying Physical Attributes

When creating an index, you can specify physical attributes that are normally generated automatically. Specifying explicit values for this information allows you to create and maintain tables that have identical physical attributes and can therefore be accessed through a single schema definition.

Example

In this example, an index has been created on the employee table. The keys in the index are LAST_NAME, FIRST_NAME. The index does not require that the last name/first name combination be unique. The index will be located physically in a separate area from the data in the table.

create index em_name_ndx on prod.employee (last_name, first_name)
    in emp.empreg1;