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

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