DB2: Unique-index Vs Clustered-index

The index on a table improves performance. In other words, an indexed Table takes less CPU time. And that fetches results faster. Here I am sharing differences between clustered index and Unique-index.

Unique Index

Let me explain it with an example, the column that you use for the UNIQUE index may not be NOT NULL.

The uniqueness you always expect from the UNIQUE index. So it does not allow duplicates.

Moreover, you can create multiple UNIQUE indexes on a Table.

Example

CREATE UNIQUE INDEX ORD_INDX
ON ODER_TABLE (ORDER_NUMBER);

Clustered Index

Only one index in a table can be a clustered index. It tries to store records on a page that has similar index keys/values. And it is designed to increase performance by decreasing I/O.

In terms of quantity, you can create only one Clustered index on a Table.

Example

CREATE UNIQUE INDEX ORD_INDX
ON ODER_TABLE (ORDER_NUMBER)
CLUSTER;

Related Posts