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

Nicely done, Martin. :-)

Have a good weekend.

TG

Martin Haltmayer wrote:

> 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 Fri Jun 14 2002 - 18:32:37 CDT