0

I recently encountered a DB2 table that has three different indexes that are unique. 

Index 1 (Columns: A, B, C)
Index 2 (Columns: A, B, C, D)
Index 3 (Columns: A, B, C, D, E)

Is the most specific one the actual unique index? Or does the definition of uniqueness differ depending about which index DB2 uses to access the table?

I'm a bit confused since, index 1 suggests that as long as my values for A, B, C are unique, I can have duplicate values for D and E. But then there's index 3 saying that A, B, C, D, E are unique, so I can't have duplicate values for D and E after all?

Jeff B
  • 8,572
  • 17
  • 61
  • 140
  • 1
    if (A, B, C) is unique, so is any superset of columns (easy to prove by contradiction) – Bwmat Jan 07 '13 at 21:13
  • So (A, B, C, D, E) is unique + extra? Not necessarily all that necessary for uniqueness? – Jeff B Jan 08 '13 at 02:05
  • Yes, that's what the existence of those unique indexes would indicate. – Bwmat Jan 08 '13 at 17:30
  • @Bwmat If you'd like to post an answer, have a go at it, otherwise I'll post one sometime. – Jeff B Dec 17 '13 at 23:29
  • What "flavor" of DB2? DB2 for i allows the three to be totally separate or physically a single index. It can depend on (1) attributes at creation time and (2) the order they were created. – user2338816 Mar 28 '14 at 02:01
  • @user2338816, unfortunately I don't really know. I'm not the DBA at my company. – Jeff B Mar 30 '14 at 06:00
  • Index1 and index2 aren't necessary. Index3 would handle all of the queries based on index1 or index2. I suspect index1 was the original unique index. Index2 was created later for a particular query, Index3 was created later for another particular query. Index3 gives a slight performance gain over index1 and index2 when the query can be satisfied by the columns in the index. – Gilbert Le Blanc Feb 26 '16 at 09:21

1 Answers1

1

Quite the opposite, the only unique index that counts is Index 1 (for uniqueness).

I haven't tried it, but for accessing purpose, DB2 would use the index that is better for the actual query you are performing.

For instance, if you are querying { A=1, B=2, C=3 } it should use Index 1; if you are querying {A =1, B=2, C=3, D=4 } it should use Index 2, even if it could just use Index 1, but you won't see any performance gain.

II ARROWS
  • 369
  • 3
  • 11
  • 1
    Index2 gives a slight performance gain over index1 if the query can be satisfied by the columns in the index. – Gilbert Le Blanc Feb 26 '16 at 09:18
  • In theory: yes. In reality, for this specific case Index2 is really not more efficient, since they both point to the same record in a atomic operation, the check to D is useless. As I said I haven't tried it and I'd like to be proved wrong, but I don't thinkg you would gain 1 s in any query because of Index2. – II ARROWS Feb 27 '16 at 10:18