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

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!