CODEWITHSUNDEEP
sqlindexingdb2uniqueunique-constraint
Udo Held
Udo Held

Reputation: 12538

DB2 unique constraint, unique indexes and performance

I have a table that requires two rows to be unique. They are likely to be joined regularly so I probably need an index on these rows.

I checked the Infocenter on the Unique constraint and on the Unique Index.

I'm wondering about the difference and the performance impact. Both seem to create an index. The unique index allows one null value. Are there other important differences?

Do these indexes improve query performance or are they just enforcing uniqueness? Should I add an additional index for performance reasons or will be the unique index be good enough? Unfortunately I don't have enough test data yet for trying it out yet.

Upvotes: 2

Views: 6028

Answers (2)

Vishnu V Leelakrishnan
Vishnu V Leelakrishnan

Reputation: 436

Unique constraint or Unique Index has no performance differences and any one would suffice. During query processing DB2 optimizer would automatically pick up indexes created for maintaining unique constraint.

Upvotes: 2

jazzytomato
jazzytomato

Reputation: 7214

You will find explanation in this topic : http://bytes.com/topic/db2/answers/185707-difference-between-unique-constraint-unique-index

Explaination in one quote :

A unique index is a physical thing whereas a unique constraint is a data modeling construct. As was already stated, unique constraint are implemented by adding a unique index (and additionally requiring the NOT NULL condition).

Upvotes: 1

Related Questions