Creating an Index (CREATE INDEX)

Creating an Index (CREATE INDEX)
An index is a structure that provides accelerated access to the rows of a table based on the
values of one or more columns (see Appendix C for a discussion of indexes and how they
may be used to improve the efficiency of data retrievals). The presence of an index can
significantly improve the performance of a query. However, since indexes may be updated
by the system every time the underlying tables are updated, additional overheads may be
incurred. Indexes are usually created to satisfy particular search criteria after the table has
been in use for some time and has grown in size. The creation of indexes is not standard
SQL. However, most dialects support at least the following capabilities:
CREATE [UNIQUE] INDEX IndexName
ON TableName (columnName [ASC | DESC] [, . . . ])
The specified columns constitute the index key and should be listed in major to minor
order. Indexes can be created only on base tables not on views. If the UNIQUE clause is
used, uniqueness of the indexed column or combination of columns will be enforced by
the DBMS. This is certainly required for the primary key and possibly for other columns
as well (for example, for alternate keys). Although indexes can be created at any time, we
may have a problem if we try to create a unique index on a table with records in it, because
the values stored for the indexed column(s) may already contain duplicates. Therefore, it
is good practice to create unique indexes, at least for primary key columns, when the base
table is created and the DBMS does not automatically enforce primary key uniqueness.
For the Staff and PropertyForRent tables, we may want to create at least the following indexes:
CREATE UNIQUE INDEX StaffNoInd ON Staff (staffNo);
CREATE UNIQUE INDEX PropertyNoInd ON PropertyForRent (propertyNo);
For each column, we may specify that the order is ascending (ASC) or descending
(DESC), with ASC being the default setting. For example, if we create an index on the
PropertyForRent table as:
CREATE INDEX RentInd ON PropertyForRent (city, rent);
then an index called RentInd is created for the PropertyForRent table. Entries will be in alphabetical
order by city and then by rent within each city.