DB2

A DB2 index is physically stored in its own index space, separate from the data in the table. DB2 will create and maintains indexes for you when you use the CREATE INDEX statement. An Index Space is a storage structure that contains a single index and it is created in the same database as its table.

The main purposes of indexes are to improve performance and to ensure row uniqueness. The optimizer decides when an index will be used to access a table, although it is possible for you to influence their usage.

In DB2 indexes can also be used to cluster data, partition data and provide index-only access to data.

An index can be either partitioning or nonpartitioning, and either type can be clustered. For example, you can partition data by date, possibly using one partition for each day. Your choice of a partitioning scheme is based on how an application accesses data, how much data you have, and how large you expect the total amount of data to grow.

Bitmapped Indexes

DB2 optimizer will create bitmap indexes dynamically when they are needed. This removes the requirement having to create and maintain static bitmap indexes. For example, you may create a bitmap index on a low cardinality column in Oracle, bit in DB2 this will be done for you. With static bitmap indexes there is an additional overhead in that each insert, update and delete needs to update the indexes. Dynamic bitmap indexes have no disk or update overhead and DB2 can give you good performance by accessing the index and handling the ROWIDS in memory.