Re: Is it inefficient to have two unique keys where the second is redundant?

I hope, but I've not taken the time to verify, that a unique key could, in
theory, be useful to the database engine, and therefore beneficial to
performance, for READ operations as well.

If you request all rows that match a key, and that key is defined as
unique, once the database engine finds one row matching the requested key,
there should be no need for the database to attempt to find additional
rows, because it is defined as unique.

Does it do that? I don't know. If it doesn't today, will it do that in
the future? Potentially yes. So, even if it isn't smart enough to do that
today, it might, and my guess is probably will, do that in the future.

Granted, that potential read benefit, should be weighed against the added
overhead of unique key enforcement for row inserts and updates.

Mike

date: Tue, 13 Jun 2017 12:45:54 +0000
from: Jonathan Wilson <piercing_male@xxxxxxxxxxx>
subject: Re: Is it inefficient to have two unique keys where the
second is redundant?

On Tue, 2017-06-13 at 07:49 -0400, Glenn Gundermann wrote:

Hi there,

I saw some code last night that made me wonder.

One registration table had two unique keys:
The first unique key: (profile, event)
The second unique key: (event, profile)

I'm wondering if it's redundant to have the second unique key since the
first unique key will ensure this.

For performance, it makes sense to have an index for (event, profile).
Is there any extra overhead or inefficiency by making it a unique key?

My guess is that that its clever enough to know that the same keys are
used so it doesn't bother to do a second record/duplicate check. That
said, even if its not that clever, I wonder if the additional record key
test would overly affect performance as I would assume that its code
that has had years of optimisations applied since the "400" first hit
the market. I guess it could be tested by writing tens of thousands of
records with and without the unique, but my instinct says the time
differences would be quite small for a large number of records (and so
small for individual records that the impact would be effectively zero
in most programs). Be interesting to know for sure now that you've
brought it up.

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 486-1162 x 239
Cell: (416) 317-3144

------------------------------

message: 3
date: Tue, 13 Jun 2017 13:24:20 +0000 (UTC)
from: Sue Baker <smbaker@xxxxxxxxxx>
subject: Re: Listing of disk drive protection features


Model #s for disk units are pretty much meaningless now as too
many models simply represent "protected". Performance tools data
in QAPMDISK has the information needed to understand the
protection at a more detailed level (RAID5, RAID6, RAID10,
etc.). The details can be located in the Knowledge Center for
the release you're running.

Steve Pavlichek
<spavlichek@xxxxxxxxxxx> wrote on
Fri, 09 Jun 2017 12:54:36 GMT:

Where can I find a listing of the disk protection “models”
which show up on a rack configuration? I used to have one for
old SCSI drives but can not find a reference for all the new
SAS drives and protection schemes.

Example from rack config:

Description Type-Model
Disk Unit xxxx-101
Disk Unit xxxx-109
Disk Unit xxxx-099
Disk Unit xxxx-051

What do the 050,051,101,109 represent?


Thank
Steve

--
Sue
IBM Washington Systems Center - Power Systems
Rochester, MN



------------------------------

Subject: Digest Footer

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

------------------------------

End of MIDRANGE-L Digest, Vol 16, Issue 999
*******************************************