Indexes

Indexes allow a DBMS to access data quicker (please note: this feature is nonstandard/not available on all systems). The system creates this internal data structure (the index) which causes selection of rows, when the selection is based on indexed columns, to occur faster. This index tells the DBMS where a certain row is in the table given an indexed-column value, much like a book index tells you what page a given word appears. Let's create an index for the CustomerID in the CarOwners column:

CREATE INDEX CID_IDX ON CAROWNERS (CUSTOMERID);

Now on the names:

CREATE INDEX NAME_IDX ON CAROWNERS (LASTNAME, FIRSTNAME);

To get rid of an index, drop it:

DROP INDEX CID_IDX;

You can also "drop" a table, as well (that means that your table is deleted). In the second example, the index is kept on the two columns, aggregated together.

Some DBMS's do not enforce primary keys; in other words, the uniqueness of a column is not enforced automatically.

CREATE UNIQUE INDEX CID_IDX ON CAROWNERS (CUSTOMERID);