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

stotejo_at_rbcel.com (John Stote) wrote:

>>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.
>Two thoughts:
 

>1) Clearly an index on ColA is required.
 

>2) Hashing makes this kind of thing very fast for the right type of
>data. Consider putting the table in a cluster along with the
>appropriate index.
 

>John..

You need an index on the table, and you could try using a co-related subquery with WHERE EXISTS between the two queries and try to limit the subquery to only one row (the optimizer may do this anyway).

You need the index to get to the row fast, and the WHERE EXISTS clause should limit the rows returned in the subquery, it can eliminate going to the table at all and just use the information in the index!

Wayne Balmer
WAINWRIGHTS, inc.
wbalmer_at_wainwrights.com
Wayne Balmer, WAINWRIGHTS, inc.
wbalmer_at_wainwrights.com
www.op.net/~wbalmer (modest home page) Received on Mon Apr 21 1997 - 00:00:00 CDT