Re: Help with SQL constraint

On Feb 16, 8:41 pm, "dean" <deanbrow..._at_yahoo.com> wrote:
> On Feb 16, 5:50 am, "William Robertson" <williamr2..._at_googlemail.com>
> wrote:
>
>
>
> > On Feb 15, 2:37 am, "dean" <deanbrow..._at_yahoo.com> wrote:
>
> > > Hello all,
>
> > > A table T has 2 fields, one (L) holding letters 'Y' and 'N', and one
> > > (X) holding numbers. Is there a (non trigger) constraint such that for
> > > L='N' (and only this letter) the numbers must be unique? Records
> > > where L='Y' do not have to be unique.
>
> > > (I need to join another table to the L='N' group of records, and the
> > > join must be key-preserved).
>
> > > Cheers,
>
> > > Dean
>
> > The standard "conditional uniqueness" approach is to use a unique
> > function based index using a DECODE or similar expression. Whether
> > this will count as a key preservation device I don't know. If not the
> > alternative would be to use the (undocumented, known to stop working
> > on patch releases) hint 'BYPASS_UJVC".
>
> William - alas 'BYPASS_UJVC is no longer supported on 10g. I already
> tried that one successfully on 9.2i and it worked well. But thanks for
> the suggestion. Maybe if you know of an equivalent in 10g you could
> enlighten me?
>
> Dean

I had the impression BYPASS_UJVC was only knocked out in one patch release and possibly worked again in a later one, though that may be wrong and obviously it's unreliable in any case.

Another alternative would be to use a cursor joining the two tables, and use bulk collect and forall with an optimal LIMIT etc and see if that approaches the performance of plain SQL.

I agree with Dan though, if this is a tuning problem then let's look at the whole picture rather than zeroing in on this faking-key- preservation issue prematurely. Received on Sat Feb 17 2007 - 06:45:16 CST