Indexes in DB2 - Part II

In this post we will talk more about the types of index and in next post how indexes works.

Theoretically speaking there can be indexes categorized in to various types but we will talk more about the types which matters most to the programmers.

Unique Index  

This is the type of index which ensures that the value in a column or set of column of table is Unique.

So Unique Index is the way using which DB2 ensures that no identical key values are stored in table.

So when the Unique index creation is must??

When you create a table, say EMP_DETAILS which has all employee details, and one of the column of this table is EMP_NO (Employee Number). You , for obvious reasons, want this column to have only unique values so you define this column as PRIMARY KEY.

The definition of this PRIMARY KEY is not complete until you define the UNIQUE INDEX on it.So you must create unique index on this PRIMARY KEY which is the only way DB2 ensures that your PRIMARY KEY will not contain any duplicate values.

Creating Unique index is very simple just add the word UNIQUE to normal CREATE INDEX definition.

CREATE UNIQUE INDEX EMPINDEX
   ON EMP_DETAILS (EMP_NO);

So once this UNIQUE INDEX is created DB2 prevents inserting any value in this table which has existing EMP_NO.

What if program tries to insert the existing value of EMP_NO in this table??

The DB2 throws error and SQLCODE is -803 which is UNIQUE INDEX violation.

Clustering Index

When you define the clustering index on the table, then this index will determine how the rows are physically stored/ordered from table in table space.

Why as a programmer i should be bothered about how the rows are physically stored in table space , you might ask? 

Well ,CLUSTERING  INDEX  provides significant performance advantage in some operations.

Examples :- When there are grouping/ordering operations are performed in programs, it is very beneficial to have clustering index on table.

This is how you create CLUSTERING INDEX on table,just add the word CLUSTER at end.

CREATE INDEX EMPINDEX
  ON EMP_DETAILS
    (EMP_NO ASC) 

     CLUSTER;