Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Q: To check a record's existence in a table, FAST!
Bud Lo wrote:
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
Isn't rownum an SQL*PLUS extension and hence only applies to the retrieved result set as obtained from the server?
-- Jonas Plumecocq jp_at_internex.com.auReceived on Thu Apr 24 1997 - 00:00:00 CDT