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

As mathematics has proven time and again,

a + b = b + a

My OPINION is that the constraint could be processed twice at the write event, potentially impacting performance.

The need for 2 unique keys to be defined as mirrors of one another is beyond my comprehension this early in the morning. You know...because a + b = b + a...right? So I would drop the unique constraint on one of these indexes...it just isn't needed.

Now...there is probably a good reason to have the access path however, so I would keep the "flipped" access path to aid the Query Engine in its quest for your data.

In a nutshell and if it were up to me...I would keep both access paths and change one so it does not have the Unique constraint.

Steve Needles


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Jones
Sent: Tuesday, June 13, 2017 12:20 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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
*******************************************

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 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



CONFIDENTIAL: The information contained in this email communication is confidential information intended only for the use of the addressee. Unauthorized use, disclosure or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by return email and destroy all copies of this communication, including all attachments.