Indexes

Indexes are mechanisms to speed up retrieval of data.

CREATE [UNIQUE|CLUSTER] INDEX index-name 
ON table-name (column-list)

DROP INDEX index-name

• unique index will ensure that two different rows do not have the same value under the indexed columns
• clustered index will ensure that rows with same values are kept next to each other on disk

CREATE UNIQUE INDEX cust_index ON customers(cid);
CREATE UNIQUE INDEX agent_index ON agents(aid);
CREATE UNIQUE INDEX prod_index ON products(pid);
CREATE UNIQUE INDEX ord_index ON orders(ordno);
CREATE CLUSTER INDEX month_index ON orders(month);