Index Types

Two groups of index types

• DB2 indexes fall into two independent groups, and an index can belong to one type from each group.
• Group 1 - by uniqueness: unique and non-unique indexes.
• Group 2 - by data ordering: clustered (clustering) and non-clustered indexes.

Unique vs non-unique indexes

• A unique index enforces uniqueness of the key - no two rows can have the same key values.
• A unique index helps maintain data integrity by rejecting duplicate keys when rows are added or changed.
• A non-unique index allows duplicate key values.
• Non-unique indexes are not used to enforce constraints - they exist solely to improve query performance by keeping a sorted order of values that are used frequently.

Clustered vs non-clustered indexes

• In a clustered index, the order of rows in the data pages corresponds to the order of rows in the index.
• With a clustered index available, new data rows are stored on the same page as other records with similar key values.
• There can be only one clustered index per table, because the rows can be physically ordered only one way.
• You can have many non-clustered indexes, although each new index increases the time it takes to write new records.

Examples

• Unique index:-
  
  CREATE UNIQUE INDEX UX_EMPNO ON EMP (EMPNO);
• Non-unique index:-
  
  CREATE INDEX IX_DEPTNO ON EMP (DEPTNO);