Re: Testing for Existence of Rows - What's Fastest?

From: Martin Haltmayer <Martin.Haltmayer_at_d2mail.de>
Date: Thu, 13 Jun 2002 16:54:56 +0200
Message-ID: <3D08B240.173E3C99@d2mail.de>

I think function-based unique indexes are an option for you as Mladen pointed out.

The trick is to create a function that will only return meaningful, testable values if the "when" condition is met:

SQL> -- We want to enforce that no two rows in table foo exist where
SQL> -- the column n has the same value mod 10. But this restriction
SQL> -- shall only apply to rows which contain the string 'xyz' in a.
SQL> create table foo (n number, a varchar2 (50));

Table created.

Elapsed: 00:00:00.40
SQL> create package foopack is
  2 function uniqval (i_n in number, i_a in varchar2) return number   3 deterministic;
  4 -- pragma restrict_references (uniqval, rnps, wnps, rnds, wnds);   5 end foopack;
  6 /

Package created.

Elapsed: 00:00:00.50
SQL> create package body foopack is
  2 function uniqval (i_n in number, i_a in varchar2) return number is   3 l_retval number;
  4 begin

  5  	     if instr (i_a, 'xyz') > 0 then
  6  		     l_retval:= mod (i_n, 10);
  7  	     else    -- instr (i_a, 'xyz') > 0 then
  8  		     l_retval:= null;
  9  	     end if; -- instr (i_a, 'xyz') > 0 then
 10  	     return l_retval;

 11 end uniqval;
 12 end foopack;
 13 /

Package body created.

Elapsed: 00:00:00.40
SQL> show errors
No errors.
SQL> create unique index foo_i on
  2 foo (foopack.uniqval (n, a));

Index created.

Elapsed: 00:00:00.50
SQL>
SQL> insert into foo (n, a) values (55, 'abc');

1 row created.

Elapsed: 00:00:00.00
SQL> insert into foo (n, a) values (55, 'abc');

1 row created.

Elapsed: 00:00:00.10
SQL> insert into foo (n, a) values (55, 'abc');

1 row created.

Elapsed: 00:00:00.00
SQL> insert into foo (n, a) values (55, 'defxyzj');

1 row created.

Elapsed: 00:00:00.10
SQL> insert into foo (n, a) values (55, 'abxyzc'); insert into foo (n, a) values (55, 'abxyzc') *
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.FOO_I) violated

Elapsed: 00:00:00.00

HTH. Martin

contrapositive wrote:
>
> "Mladen Gogala" <mgogala_at_adelphia.net> wrote in message
> news:acokls$r6lfo$1_at_ID-82084.news.dfncis.de...
> > On Sat, 25 May 2002 13:45:32 -0400, contrapositive wrote:
> >
> > Well, the first thing that comes to mind is a unique index. They are
> > usually pretty good at determining uniqueness. If the property is
> > complicated, try with a function-based index.
> >
> Manipulating indexes really aren't an option, but that wouldn't help me much
> anyway. This uniqueness is only enforced only on particular types of records
> (there's a WHEN clause on the trigger), and furthermore records don't have
> to be unique throughout the table (see the WHERE clause in the original
> posting). I should have mentioned these things up front.
Received on Thu Jun 13 2002 - 09:54:56 CDT