Index

Index

In DB2, an index is a database object that enhances data retrieval speed by providing a structured pathway to access rows in a table. Indexes are separate from the table data and are stored in their own index space.

A table index is created on a table column or set of columns to optimize search operations. Indexes store sorted values of specified columns, allowing DB2 to perform efficient lookups, sorting, and query processing. Indexes benefit large tables or frequently queried columns, though they add overhead to data modification operations like INSERT, UPDATE, and DELETE.

Purpose -

  • Performance Improvement: Accelerates data retrieval operations.
  • Uniqueness Enforcement: Ensures that specific columns contain unique values.
  • Data Clustering: Organizes data rows in a particular order to optimize access patterns.
  • Partitioning: Assists in dividing data into partitions for efficient management.
  • Index-Only Access: Allows queries to be satisfied using only the index, without accessing the table data.

Syntax -

CREATE [UNIQUE] INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
[CLUSTER]
[PARTITIONED]
[PADDED | NOT PADDED]
[DEFINE YES | NO]
[COMPRESS YES | NO]
[BUFFERPOOL bpname]
[CLOSE YES | NO]
[DEFER YES | NO]
[PIECESIZE integer K | M | G]
[COPY YES | NO];
  • UNIQUE: Ensures all values in the index are distinct.
  • ASC / DESC: Specifies ascending or descending order for the index keys.
  • CLUSTER: Defines the index as clustered.
  • PARTITIONED: Indicates a partitioned index.
  • PADDED / NOT PADDED: Determines how variable-length columns are handled in the index.
  • DEFINE: Specifies whether the underlying datasets for the index are created immediately.
  • COMPRESS: Indicates whether the index should be compressed.
  • BUFFERPOOL: Assigns a buffer pool for the index.
  • CLOSE: Specifies whether the dataset can be closed when not in use.
  • DEFER: Determines if the index creation is deferred.
  • PIECESIZE: Sets the maximum size for non-partitioned index datasets.
  • COPY: Indicates if the index is included in copy operations.

Examples - 

CREATE UNIQUE INDEX emp_idx
ON employee (emp_id ASC)
CLUSTER
BUFFERPOOL BP0
CLOSE YES;

This statement creates a unique, clustered index named emp_idx on the emp_id column of the employee table, using buffer pool BP0 and allowing the dataset to be closed when not in use.

Types of Indexes -

  • Unique Index: Ensures all values in the indexed column(s) are unique.
  • Clustered Index: Arranges table data in index order, optimizing range queries.
  • Non-Unique Index: Allows duplicate values and aids in faster retrieval.
  • Composite Index: Index on multiple columns for queries with multiple criteria.