efficient way to check if record exists

944524

Hi,

I need to check if atleast one record present in table before processing rest of the statements in my PL/SQL procedure. Is there an efficient way to achieve that considering that the table is having huge number of records like 10k

I am using like below

select count(*) into flag

from T1 where ID = input_id;

if flag > 0

then perform operations

else

do nothing

Pavan Kumar

Hi,

1. How frequent you check the condition ?

2. You can create an associate array (indexing <type > based on lookup) , you can load the collection index as lookup value (it allows whether your collection is sequential or non-sequential)

3.  just look up for value (it's directly maps to location of index with in all rows )

4. You can test it, hope it works

- Pavan Kumar N

Solomon Yakobson

1. Is ID unique? There is no need to count all rows with that ID if ID is non-unique. The following will be more efficient:

select  count(*)

  into  flag

  from  T1

  where ID = input_id

    and rownum = 1;

2. What operation do you perform if flag > 0? No need for checking if at least one record present in table if performing SQL. For example, if you want to insert when no rows with such ID are in the table:

insert

  into T1

  select  ...

    from  dual

  where 0 = (

             select  count(*)

               from  T1

               where ID = input_id

                 and rownum = 1

            );

And use MERGE if you want to insert rows with such ID are in the or update when rows with such ID are in the table.

SY.

Unknown

I need to check if atleast one record present in table before processing rest of the statements in my PL/SQL procedure. Is there an efficient way to achieve that considering that the table is having huge number of records like 10k 

Yes - just count ONE ROW and then stop.

 I am using like below 

select count(*) into flag
from T1 where ID = input_id;

Why are you counting ALL rows? You said you just wanted ONE ROW.

1. You are standing outside a movie theater

2. Unknown to you there are 500 people in the theater

3. You are ask to go inside and determine if there are ANY (ii.e. at least one) persons inside

4. Then you are to come back outside and report if the theater is empty or not

Do you::

A. go inside and count EVERY PERSON in the theater and then report that the theater is NOT empty?

B. go inside, see ONE PERSON and then report that the theater is NOT empty?

Which answer did you choose? Please post your answer and explain why you chose that answer.

944524

Hi,

I agree with your point instead of counting every records its always best to check if any one the record present in table. So can you advise on that my requirement is to check if any one record present in table T1 then proceed further with the program else don't proceed.

So as suggested by solomon, i can use the below query because ID is primary key for the table or is there any other way like using EXISTS , i mean is it possible to use exists

select  count(*)

  into  flag

  from  T1

  where ID = input_id

    and rownum = 1;

Frank Kulash

Hi,

944524 wrote:

... i mean is it possible to use exists ...

Sure, you can use EXISTS if you want to:

SELECT  1
INTO    flag
FROM    dual
WHERE   EXISTS (
                   SELECT  1
                   FROM    t1
                   WHERE   id  = input_id
               )
;

This will raise the NO_DATA_FOUND exception when input_id is not in the id column.

Utsav

You can try to directly handle it inside SQL rather than doing it in PL/SQL

ReemaPuri

FOR SQL :-

yOU CAN USE NVL if you want to handle no_data_found error in count itself

if now row found with specified condition it will return 0

SELECT

COUNT(NVL(COLUMN_NAME,0))  FROM TABLENAME

WHERE COLUMN=VALUE;

SELECT

COUNT(NVL(COLUMN_NAME,0))  FROM T1

where ID = input_id;

FOR PLSQL:-

otherwise you can go for writing  exception

EXCEPTION WHEN NO_DATA_FOUND

DECLARE

L_COUNT NUMBER;

BEGIN

SELECT

COUNT(*)  INTO L_COUNT FROM T1

where ID = input_id;

BEGIN

EXCEPTION WHEN NO_DATA_FOUND THEN

L_COUNT:=0;

END;

END;

Marwim

You never get NO_DATA_FOUND with COUNT. So NVL is not necessary.

Regards

Marcus

ReemaPuri

thank you for pointing out

SELECT nvl(max(column_name),0)  FROM  tablename  where columnname=value;

SELECT

nvl(max(column_name),0)   FROM T1

where ID = input_id;

this will handle null and no data found both

Solomon Yakobson

Yes it will but at higher cost NVL + MAX instead of just COUNT and if ID isn't unique MAX will read ALL rows where ID = input_id while COUNT + ROWNUM = 1 will read just one row.

SY.