RE: conditional unique index to restrict duplicates of a particular set
- From: "Cunningham, Mike" <mcunningham@xxxxxxxxxxxxxx>
- To: "carlos.sierra.usa@xxxxxxxxx" <carlos.sierra.usa@xxxxxxxxx>, Mohamed Houri <mohamed.houri@xxxxxxxxx>
- Date: Wed, 7 May 2014 10:46:22 -0700
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Carlos Sierra
Sent: Wednesday, May 07, 2014 8:32 AM
To: Mohamed Houri
Cc: ftaheny@xxxxxxxxx; oracle-l-freelists; vijaysehgal21@xxxxxxxxx
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@xxxxxxxxx<mailto:mohamed.houri@xxxxxxxxx>> 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@xxxxxxxxx<mailto:ftaheny@xxxxxxxxx>>:
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.
Other related posts:
