DB2: Unique-index Vs Clustered-index

The performance of the Table directly links to the index. 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 key/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

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.