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

On Tue, 29 Apr 1997 07:28:24 -0500, Marc <marcm_at_computek.net> wrote:

>Bud Lo wrote:
>>
>> To check a record's existence in a table. ( i am using Oracle 7)
>
>I have read your proposed solution and other's response. they are all
>feasible. However, I have one question for you:
>
>Why do you want to check a records existance?
>
>Many times the reason is to know whether to insert or to update. If
>this is the case then the most efficient method is to attempt the
>update and check to see if there is NO_DATA_FOUND. If there is
>NO_DATA_FOUND then do the insert. You will have saved one fetch
>over doing a check, then either an insert or update.
>I'm not sure what your purpose for the check is but many many times
>the above reason is the case.

In my case, it's not check for update or insert, my main use is for resource deallocation and validation. e.g. I need to check whether an allocated resource is still being used by some entities. If no entity is using it (i.e., count(rowid)=0) then i can deallocate the resource.

Our system is designed for use in Telephone Exchange network, you might have heard the telephone switch system like NEAX, FETEX and terminology like TGN,TGX, NOC, ROUTE, etc...

>Also, use count(rowid) instead of
>count(*). The count(*) has to retrieve the row from the table block
>where as the count(rowid) does not. The count(*) also counts NULLs
>where as the count(rowid) counts only row id's.
>

thanks i've learnt one more thing, i'll switch to count(rowid)

Bud Received on Tue Apr 29 1997 - 00:00:00 CDT