DB2 - Indexes

This chapter covers introduction to indexes, their types, creation and dropping.

Introduction

Index is a set of pointers, which can refer to rows in a table, blocks in MDC or ITC tables, XML data in an XML storage object that are logically ordered by the values of one or more keys. It is created on DB2 table columns to speed up the data access for the queries, and to cluster and partition the data efficiently. It can also improve the performance of operation on the view. A table with a unique index can have rows with unique keys. Depending on the table requirements, you can take different types of indexes.

Types of indexes

Unique and Non-Unique indexes

Clustered and non-clustered indexes

Creating indexes

For creating unique indexes, you use following syntax:

Syntax:

db2 create unique index <index_name> on 
<table_name>(<unique_column>) include (<column_names..>) 

Example: To create index for “shopper.sales1” table.

db2 create unique index sales1_indx on 
shopper.sales1(id) include (itemname) 

Dropping indexes

For dropping the index, you use the following syntax:

Syntax:

db2 drop unique index <index_name> on 
<table_name>(<unique_column>) include (<column_names..>) 

Example:

db2 drop index sales_index