By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,078 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,078 IT Pros & Developers. It's quick & easy.

Unique Vs. Non-Unique Indexes

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/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.
Nov 12 '05 #2

P: n/a
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.
Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

P: n/a
> 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

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.