Index

An index contains pointers to the rows of a table, ordered by the values of one or more columns. It lets DB2 find rows directly instead of scanning the whole table, so data can be accessed more efficiently.

What is an index

• An index holds pointers ordered on the value of data in the specified columns of the table.
• A unique index also enforces uniqueness - no two rows can have the same key value.
• A clustering index decides the physical order of rows in the tablespace, which speeds up sequential access.
• DB2 creates an indexspace automatically when you run CREATE INDEX - an indexspace holds only one index.
• Indexes speed up SELECT but slow down INSERT, UPDATE and DELETE, because the index must be maintained too.

CREATE INDEX - example

• A unique clustering index on the customer number:-
  
  CREATE UNIQUE INDEX MM01.CUSTNDX ON MM01.CUSTOMER (CUSTNO ASC) USING STOGROUP SG1 PRIQTY 144 CLUSTER;
• UNIQUE enforces one row per CUSTNO, ASC orders the keys ascending, and CLUSTER stores the table rows in CUSTNO order.
• An index is removed with DROP INDEX MM01.CUSTNDX;