RE: conditional unique index to restrict duplicates of a particular set

From: Cunningham, Mike <mcunningham_at_thedoctors.com>
Date: Wed, 7 May 2014 10:46:22 -0700
Message-ID: <C8FDF1081BF81B418F937FE98B7B3CC7A85A56_at_NPEXCHMB102.tdc.internal>

Hi, would the scalability issue be due to transaction management? I think the unique rule could be broken if using a trigger since two transactions from different sessions can't see each others data until after a commit. Session B could have checked for uniqueness prior to session A committing its data. If session A commits, then session B commits (the same data) the unique rule would be broken. Am I missing something in my reasoning?

Michael Cunningham
Senior Database Administrator
The Doctors' Company
707.226.0221 - desk
707.337.0184 - cell

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Carlos Sierra Sent: Wednesday, May 07, 2014 8:32 AM
To: Mohamed Houri
Cc: ftaheny_at_gmail.com; oracle-l-freelists; vijaysehgal21_at_gmail.com Subject: Re: conditional unique index to restrict duplicates of a particular set

Mohamed,

Would you please elaborate why an INSERT/UPDATE trigger using a non-unique index on those 4 columns would not scale? The trigger would do an index lookup using the 4 columns when the set of values passed is expected to be unique and raise an error if there exists such set. This method would be clean, and the index could be used by other queries.

Cheers — Carlos

On May 7, 2014, at 9:50 AM, Mohamed Houri <mohamed.houri_at_gmail.com<mailto:mohamed.houri_at_gmail.com>> wrote:

I am wondering how you will be able to enforce uniqueness via a trigger in a multi-user concurent application? It will not scale. You need either to use your unique function based index or, if in 11gR1 and up create a virtual column and place a unique constraint (unique index) on this virtual column

Best regards
Mohamed Houri

2014-05-07 15:13 GMT+02:00 Fergal Taheny <ftaheny_at_gmail.com<mailto:ftaheny_at_gmail.com>>:

Hi Carlos,

Won't you face a mutating table error if you try to use a trigger for this.

Regards,
Fergal

--

Bien Respectueusement
Mohamed Houri

Confidentiality Notice: This message and any attachments hereto may contain confidential and privileged communications or information and/or attorney client communications or work-product protected by law. The information contained herein is transmitted for the sole use of the intended recipient(s). If you are not the intended recipient or designated agent of the recipient of such information, you are hereby notified that any use, dissemination, copying or retention of this e-mail or the information contained herein is strictly prohibited and may subject you to penalties under federal and/or state law. If you received this e-mail in error, please notify the sender immediately and permanently delete this e-mail.
--

http://www.freelists.org/webpage/oracle-l Received on Wed May 07 2014 - 19:46:22 CEST