Re: conditional unique index to restrict duplicates of a particular set
Date: Wed, 7 May 2014 13:05:31 -0500
Message-ID: <CAJueESquaWE+KSHFamKw5qf3S_6zi=ohEJSNR1AVoZLfJrCmoQ_at_mail.gmail.com>
Concurrency, at the very least, would be a likely issue with this approach. It can be worked around (Tom Kyte has a few examples, IIRC), but the complexity of the trigger-based method increases *considerably* in that case.
Consider 2 sessions trying to insert the same values concurrently. Neither can see the other's in-flight data, so the trigger doesn't raise an exception for either session. As soon as they've both committed, you have duplicated values.
Direct-path loads would be another potential scenario.
On Wed, May 7, 2014 at 10:32 AM, Carlos Sierra <carlos.sierra.usa_at_gmail.com>wrote:
> 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> 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>:
>
>> 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
>
>
>
-- "I'm too sexy for my code." -Awk Sed Fred -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 07 2014 - 20:05:31 CEST