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

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
Received on Sat Apr 19 1997 - 00:00:00 CDT