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

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

Hope this helps.

Chrysalis.

Bud Lo wrote:
>
> Hi all,
>
> My requirement is very simple.
>
> To check a record's existence in a table. ( i am using Oracle 7)
>
> Obviously, this SQL will do:
>
> select count(*) into nCount from table where ColA='ABC';
>
> But i only want to know whether the record
> exists or not, the exact count of record is immaterial.
> As my table is very large, the count(*) will scan
> for all records in the table. My required behavior is that
> the SQL returns whenever the first record found and stop
> further scanning. So it would be faster.
>
> The solution i thought:
>
> 1st
> ===
> nCount := 0;
>
> select 1 into nCount from dual
> where exists (select * from table where ColA='ABA');
>
> 2nd (Don't sure whether it performs as i required)
> ==================================================
> select count(*) into nCount from table where ColA='ABC' and
> rownum=1;
>
> I want to find the elegant solution to this problem,
> 2nd alternative will do if it stop scanning when
> 1st record is found, anyone can confirm? or anyone
> suggest a better solution?
>
> thanks,
> Bud
Received on Sat Apr 19 1997 - 00:00:00 CDT