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

"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
> >
> >
> >
>
>

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)..

Have you ever tried this? It is simply not true. Rownum's aren't determined dynamically, they are determined when the result set is complete.
So you are actually fetching *ALL* rows that satisfy the where clause, and cutting them off after fetching.
Try it, with set autotrace on explain stat, and you will see I am correct.

Regards

--
Sybrand Bakker
Senior Oracle DBA

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