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

Bud Lo wrote:
>
> Hi all,
>
> My requirement is very simple.
>
> 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. 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.

Hope this helps,
Marc

-- 
||----------------------------------------------------------------------||
|| Marc Marchioli
|| The DataBase Group, Inc.                       voice:  214-528-9459
|| 4011 Travis St.                                fax:    214-528-9459
|| Dallas, TX 75204-7512
|| marcm_at_computek.net                         @
|| (Challenging ORACLE and UNIX every day)    |-}ORACLE,UNIX
||                                            /\-------------->