Unique Vs. Non-Unique Indexes

[bwmiller16]

Guys -

I'm doing a database consistency check for a client and I find that
they're building unique indexes for performance/query reasons where
they could be using non-unique indexes.

Note that these columns in the unique indexes are truly unique and
don't constitute a collision hazard of any kind.

Now, I personally wouldn't use unique where non-unique would do but I
need to know what the performance impact here is.

What's it costing to use unique when non-unique would do?

DB2 8.1.8 on AIX 5.1

Bruce

[Mark A]

<bw********@yahoo.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Guys -

I'm doing a database consistency check for a client and I find that
they're building unique indexes for performance/query reasons where
they could be using non-unique indexes.

Note that these columns in the unique indexes are truly unique and
don't constitute a collision hazard of any kind.

Now, I personally wouldn't use unique where non-unique would do but I
need to know what the performance impact here is.

What's it costing to use unique when non-unique would do?

DB2 8.1.8 on AIX 5.1

Bruce

The only performance impact that I know of is when you insert a row or
update one of the columns. Then DB2 will have to make sure the index entry
does not already exist. But since it is adding the index row anyway when you
insert the row (or updating it if you change one of the columns in the
index) then I doubt it is much more overhead.

[Bob Jones]

Guys -

I'm doing a database consistency check for a client and I find that
they're building unique indexes for performance/query reasons where
they could be using non-unique indexes.

Note that these columns in the unique indexes are truly unique and
don't constitute a collision hazard of any kind.

Now, I personally wouldn't use unique where non-unique would do but I
need to know what the performance impact here is.

What's it costing to use unique when non-unique would do?

DB2 8.1.8 on AIX 5.1

In some cases, queries perform better when using unique indexes.

[Brian Tkatch]

The data model should demand UNIQUE or non-UNIQUE. If it doesn't
perhaps you have a much larger problem.

There's probably a slight performance impact when searching for a
value, because with a UNIQUE INDEX once a values is found, the database
no longer needs to search for more values. Which in this case would be
searching for one more row and not finding it. The difference should be
terribly negligable, however.

IIRC, on some platforms, DB2 demands a UNIQUE INDEX on the parent
COLUMN of a FOREIGN KEY.

B.

[Mark Yudkin]

> What's it costing to use unique when non-unique would do?

The question would be better posed as "What's it costing to use non-unique
when unique would do?" To which the answer is loss of semantic, and non
infrequent worse performance on a range of query types.

<bw********@yahoo.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Guys -

I'm doing a database consistency check for a client and I find that
they're building unique indexes for performance/query reasons where
they could be using non-unique indexes.

Note that these columns in the unique indexes are truly unique and
don't constitute a collision hazard of any kind.

Now, I personally wouldn't use unique where non-unique would do but I
need to know what the performance impact here is.

What's it costing to use unique when non-unique would do?

DB2 8.1.8 on AIX 5.1

Bruce