A Clustered index is one of the special types of index which reorders the way records in the table are physically stored on the disk. It sorts and stores the data rows in the table or view based on their key values. It is essentially a sorted copy of the data in the indexed columns.
Sometimes we are asked to create an index on a non-unique key like dept-id in the below table. There could be several employees in each department. Here, all employees belonging to the same dept-id are considered to be within a single cluster, and the index pointers point to the cluster as a whole.
Given below is an example of the clustering index −
In the above diagram, the disk blocks contain a fixed number of records. The index contains entries for 5 separate departments. The pointers of these entries point to the first record of the block.
The blocks themselves may point to the next block in case a cluster overflows a block size. This can be done using a special pointer at the end of each block.
The previous scheme might become a little confusing because one disk might be shared by records belonging to different clusters. A better scheme could be to use separate disk blocks for separate clusters.
This is explained below −
This scheme takes more space in the memory but the organization is much better and cleaner looking.