What is a clustered index in DB2 and how is it different from a non-clustered index?

What is a Clustered Index in DB2 and How is it Different from a Non-Clustered Index?

A clustered index in DB2 is an index that organizes the physical order of data in a table according to the order of the index key. This means that the actual data rows are stored on disk in the same order as the index key values. In other words, the data in the table is physically sorted based on the indexed column's values.

In contrast, a non-clustered index in DB2 does not dictate the physical order of data rows on disk. Instead, it contains a separate structure that points to the actual rows in the table. This means that the data rows are stored separately from the index structure, and the rows are not physically sorted based on the index key values.

One key difference between a clustered and non-clustered index is that a table can have only one clustered index, as the data rows can be physically ordered based on only one key. On the other hand, a table can have multiple non-clustered indexes, each pointing to the table's data rows in a different order.

When querying data, a clustered index can generally provide faster performance for range queries and queries that require sorting by the index key. Non-clustered indexes are typically better suited for searches that involve key lookups or queries that don't rely on the physical order of the data rows.

In summary, a clustered index in DB2 physically orders the data rows in a table based on the index key values, while a non-clustered index does not. Each type of index has its own advantages and use cases depending on the specific requirements of the queries being executed.