Re: conditional unique index to restrict duplicates of a particular set
- From: Adric Norris <landstander668@xxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 May 2014 13:05:31 -0500
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@xxxxxxxxx>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@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>:
>
>> 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
Other related posts:
