Understanding database [6] : Clustered Index

A clustered index is what is good for a range search over a range of search key values, and the index entries and the rows are ordered the same way, which is different from unclustered indexes (secondary indexes). To use a clustered index, we use the index to locate the first index entry at the start of the range, which where the first row is located at. If the index is clustered, subsequent rows will be stored in successive locations with the ordering , therefore it may minimize the page transfers and maximizes cache hits.

For one table, there may only be one clustered index, whereas there can be as many unclustered indexes as you may want  to create. Unclustered indexes aren’t as good as clustered, but they might become necessary when it comes to finding for other attributes apart from the primary key.

Smaller topics :

Dense index & Sparse index

When we say sparse index, we mean that there is an index entry for each page of the data file. With this structuring of indexes, the search key should be the candidate key of the data file. On the other hand, dense indexes will have an index entry for each data record, and this is a compulsory requirement for an clustered index.

Primary index

An index whose search key specifies the sequential order of the file, typically implemented by index entries containing actual data rows.

Unique index

An index over a candidate key