Clustering Index

What is a clustering index?

• A clustered index is an index whose order of the rows in the data pages corresponds to the order of the rows in the index.
• If a clustered index is available for a table, data rows are stored on the same page where other records with similar index keys are already stored.
• In other words, the physical order of the rows follows the key order of the clustering index.
• There can be only one clustered index for a table, because rows can be physically stored in just one order.

Why it helps

• Range queries (BETWEEN, >, <) on the clustering key read fewer pages, because neighboring key values sit on neighboring data pages.
• ORDER BY on the clustering key is cheap - the rows are already in that order.
• Batch programs that process a table sequentially benefit the most from a clustering index.
• You can still have many non-clustering indexes, although each new index increases the time it takes to write new records.

Example

• Create a clustering index:-
  
  CREATE INDEX IX_EMP_DEPT_CL ON EMP (DEPTNO) CLUSTER;
• The CLUSTER keyword tells DB2 to keep the EMP rows physically ordered by DEPTNO.
• Note: the first index you create on a table becomes the clustering index by default, unless you specify otherwise.