Difference between Clustered and Non-Clustered Indexes in DB2

Clustered and non-clustered indexes are two types of indexes used in DB2 for organizing and accessing data efficiently.

Clustered Indexes:

A clustered index in DB2 physically rearranges the data in the table based on the indexed column. This means that the rows in the table are stored in the same order as the index, which can improve the performance of queries that involve the indexed column.

Non-Clustered Indexes:

In contrast, a non-clustered index in DB2 does not affect the physical order of the data in the table. Instead, it creates a separate data structure that points to the actual rows in the table based on the indexed column. Non-clustered indexes are useful for improving the performance of searches and other queries that do not require the data to be stored in a specific order.

Main Differences:

• Clustered indexes physically rearrange data, while non-clustered indexes do not.
• Clustered indexes can improve the performance of queries involving the indexed column, while non-clustered indexes are more versatile for various types of queries.
• Only one clustered index can be created per table in DB2, while multiple non-clustered indexes can be created.