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

"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:
>
> > I've discovered a trigger in our database that is a real resource hog.
> > The trigger happens AFTER INSERT on tableA and it looks something like
> > this...
> >
> > SELECT count(colA)
> > INTO varCount
> > FROM tableA -- has ~300,000 rows
> > WHERE... ;
> >
> > IF varCount > 1 THEN
> > RAISE_APPLICATION_ERROR... ;
> > END IF;
> >
> > All it's doing is insuring that no record exists with the same
particular
> > property of the one just inserted (hence > 1). This is all fine, but
>
> 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.
>
> --
> Mladen Gogala
> Za Feral Spremni!

How about

select 'x'
from dual
where exists
(select 'x'
 from tableA
 where ...
)
Definitely guaranteed to retrieve *one* key for tableA *only* Ugly solution, but it works.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address