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

From: Bud Lo <budlo_at_netvigator.com>
Date: 1997/04/19
Message-ID: <335996ab.33078892@news.netvigator.com>#1/1

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