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

On Sat, 19 Apr 1997 16:39:13 +0100, Jim Smith <jim_at_jimsmith.demon.co.uk> wrote:

>
>In article <335b45b1.12345729_at_news.netvigator.com>, Bud Lo
><budlo_at_netvigator.com> writes
>>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?
>>
>>
>The first option is bestt, but neither will perform very well without a
>(unique ?) index on colA.
>
>The second option won't work as you expect. A count(*) only returns one
>row so the rownum=1 is meaningless.
>--
>Jim Smith

The row count is immaterial to me, i just want to know if any record exists or not. The count(*) used in second option is used just to facilitate the checking for value return (0 or 1) in a SQL statement . like

select count(*) into nCount from table where x='aa' and rownum=1; if nCount = 1 then
 ....
end if;

The reason i prefer 2nd option is that it is more compact and less coding, but not sure whether it would fetch all records matching the condition and return the 1st record OR it would only fetch the 1st found record and return.

thanks,
Bud Received on Sun Apr 20 1997 - 00:00:00 CDT