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

If it's going to be done this way (as opposed to the function-based solution),
then this really should be
and rownum < 3
since it's done after the insert, so the new row is one of them and we're looking to see if there is a second one there already.

Neil

"Alun Liggins" <a.liggins_at_btinternet.com> wrote in message news:acqeda$bl1$1_at_helle.btinternet.com...
>
> Well you can shorten it slightly by using 'rownum'
> SELECT count(colA)
> INTO varCount
> FROM tableA -- has ~300,000 rows
> WHERE...
> AND
> rownum <2;
>
> rownum is the number of rows that have matched so far in the query.
> This should end the query when it finds the first occurance, rather than
> look at every row in the table (i.e. partial full tablescan vs full
> tablescan)..
> This may not make it any better though.
> Can you index any of the bits of the columns the WHERE clause is looking
at?
> [Do some explain plans to prove it will use it]
> A non-unique (+ the rownum bit) or better still a unique index (or primary
> key) should really be used and it will error on the insert bit of the
> trigger so you then have to trap it, although it sounds like you need to
> re-evaluate the application design to see if there is a better way.
>
> Alun
>
> "contrapositive" <contrapositive_at_hotmail.com> wrote in message
> news:3cefd19e_5_at_nopics.sjc...
> > 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
> there's
> > got to be a better way to write the query. COUNT(*) gives us more
> > information that we need, and we take a performance hit because of it.
My
> > thought was something like this...
> >
> > BEGIN
> > SELECT colA
> > INTO varA
> > FROM tableA
> > WHERE... ;
> > EXCEPTION WHEN TOO_MANY_ROWS THEN
> > RAISE_APPLICATION_ERROR... ;
> > EXCEPTION WHEN OTHERS THEN
> > NULL;
> > END;
> >
> > But this is a trigger, so if there's a more optimal way yet, I'd be
> curious
> > to see it. Thanks again...
> >
> > -jk
> >
> >
> >
>
>
Received on Sun May 26 2002 - 07:59:07 CDT