What is an Index?

An index is one of the most important performance objects in DB2. The diagram below shows how an index points to table rows:-

Explanation for the above diagram is as follows:-

What is an index?

• An index is a set of pointers which can refer to rows in a table.
• It is created on one or more columns of a DB2 table to speed up data access for queries.
• The index entries are ordered based on the value of the key columns, so DB2 can jump straight to the wanted rows instead of scanning the whole table.
• An index is stored in an index space. DB2 automatically creates the index space when you run CREATE INDEX.
• An index can also improve performance of operations on views and help cluster or partition the data efficiently.

Why use an index?

• Faster data retrieval - DB2 finds rows through the index instead of reading every page of the table.
• A table with a unique index can have rows with unique keys, which protects data integrity.
• Indexes support efficient ordering and grouping for ORDER BY and GROUP BY queries.
• But too many indexes slow down INSERT, UPDATE and DELETE, because DB2 must update every index on the table.

Example

• Example:-
  
  CREATE INDEX IX_EMPNO ON EMP (EMPNO);
• Above statement creates an index named IX_EMPNO on the EMPNO column of the EMP table.
• Drop an index you no longer need:-
  
  DROP INDEX IX_EMPNO;