Indexing and Hashing:Index Definition in SQL

Index Definition in SQL

The SQL standard does not provide any way for the database user or administrator to control what indices are created and maintained in the database system. Indices are not required for correctness, since they are redundant data structures. However, indices are important for efficient processing of transactions, including both update transactions and queries. Indices are also important for efficient enforcement of integrity constraints. For example, typical implementations enforce a key declaration (Chapter 6) by creating an index with the declared key as the search key of the index.

In principle, a database system can decide automatically what indices to create.

However, because of the space cost of indices, as well as the effect of indices on update processing, it is not easy to automatically make the right choices about what indices to maintain. Therefore, most SQL implementations provide the programmer control over creation and removal of indices via data-definition-language commands.

We illustrate the syntax of these commands next. Although the syntax that we show is widely used and supported by many database systems, it is not part of the SQL:1999 standard. The SQL standards (up to SQL:1999, at least) do not support control of the physical database schema, and have restricted themselves to the logical database schema.

We create an index by the create index command, which takes the form

create index <index-name> on <relation-name> (<attribute-list>)

The attribute-list is the list of attributes of the relations that form the search key for the index.

To define an index name b-index on the branch relation with branch-name as the search key, we write

create index b-index on branch (branch-name)

If we wish to declare that the search key is a candidate key, we add the attribute unique to the index definition. Thus, the command

create unique index b-index on branch (branch-name)

declares branch-name to be a candidate key for branch. If, at the time we enter the create unique index command, branch-name is not a candidate key, the system will display an error message, and the attempt to create the index will fail. If the index- creation attempt succeeds, any subsequent attempt to insert a tuple that violates the key declaration will fail. Note that the unique feature is redundant if the database system supports the unique declaration of the SQL standard.

Many database systems also provide a way to specify the type of index to be used (such as B+-tree or hashing). Some database systems also permit one of the indices on a relation to be declared to be clustered; the system then stores the relation sorted by the search-key of the clustered index.

The index name we specified for an index is required to drop an index. The drop index command takes the form:

drop index <index-name>