Equal unique index (MATCHCOLS=number of index columns)

An index that is fully matched and unique, and in which all matching predicates are equal-predicates, is called an equal unique index.

Introductory concepts

Begin program-specific programming interface information. This case guarantees that only one row is retrieved. If one-fetch index access is unavailable, this is considered the most efficient access over all other indexes that are not equal unique. (The uniqueness of an index is determined by whether or not it was defined as unique.)

Sometimes Db2 can determine that an index that is not fully matching is actually an equal unique index case. Assume the following case:

Unique Index1: (C1, C2)
Unique Index2: (C2, C1, C3)
 
SELECT C3 FROM T
  WHERE C1 = 1 AND C2 = 5;

Index1 is a fully matching equal unique index. However, Index2 is also an equal unique index even though it is not fully matching. Index2 is the better choice because, in addition to being equal and unique, it also provides index-only access. End program-specific programming interface information.