Re: Q: To check a record's existence in a table, FAST!

Chrysalis wrote:

1. I don't understand your objection to using a cursor. It is *always* faster than a "select ... into ..." if you only do a single fetch.
2. However, the solution with "select ... [into ...] from dual where

  exists (subquery)" is perfectly efficient, since you don't actually   need
  the row: just to know that (at least) one exists.   3) Rownum is of such restricted use that I generally avoid it, since

  there is nearly always a better solution.

  Chrysalis.

  Bud Lo wrote:
>
> On Sat, 19 Apr 1997 13:05:48 +0000, Chrysalis <cellis_at_iol.ie>
 wrote:
>
> >*Never* select count(*) unless you really want to count all the
 rows!
> >Your first solution (select null from dual where xists (subquery)
 will
> >do, since an <exists> subquery stops when the first row is found.
 

> >
> >However, I notice you are using select .. into ...
> >Does this mean you are using PL/SQL or SQL*Forms or what?
> >
> > Many questions in this NG are concerned with returning the
 first row
> >(or first n rows) and people seem to want a solution which works
 in
> >SQL*Plus, but SQL*Plus is not the best vehicle for this, since
 you have
> >no direct control over the number of rows fetched (rownum is
 almost
> >useless unless you don't care about sequence).
> > Almost any other interface (e.g. PL/SQL, Forms, etc) allow you
 to
> >declare and open a cursor and then fetch exactly the number of
 rows you
> >want.
> >
>
> i forgot to mention that i don't want to use cursor to achieve
 that.
> As you mention, if the sequence is immaterial, is the rownum
> solution works as i required?? That is, it stops when the
> 1st record is found, if any, and returns.
>
> Thanks,
> Bud

 Off the topic, is 1) due to the fact that using an explicit cursor avoids a second fetch to determine if a too many rows error should be raised? I think I read this somewhere...?

--
Jonas Plumecocq
jp_at_internex.com.au