Forum Stats

  • 3,826,860 Users
  • 2,260,714 Discussions
  • 7,897,105 Comments

Discussions

efficient way to check if record exists

944524
944524 Member Posts: 555
edited Jul 19, 2015 4:07PM in SQL & PL/SQL

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

ReemaPuriinvalidsearchFatMartinRJonathan Lewis
«13

Answers

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jul 12, 2015 8:31AM

    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
    Solomon Yakobson Member Posts: 19,482 Red Diamond
    edited Jul 12, 2015 10:14AM

    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.

    invalidsearch
  • Unknown
    edited Jul 12, 2015 11:48AM
    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
    944524 Member Posts: 555
    edited Jul 12, 2015 12:53PM

    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
    Frank Kulash Member, Moderator Posts: 42,067 Red Diamond
    edited Jul 12, 2015 5:10PM

    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
    Utsav Member Posts: 859 Silver Badge
    edited Jul 13, 2015 1:23AM

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

  • ReemaPuri
    ReemaPuri Member Posts: 1,358 Gold Badge
    edited Jul 13, 2015 2:56AM

    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

    SELECTCOUNT(NVL(COLUMN_NAME,0))  FROM TABLENAME

    WHERE COLUMN=VALUE;

    SELECTCOUNT(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

    SELECTCOUNT(*)  INTO L_COUNT FROM T1

    where ID = input_id;

    BEGIN

    EXCEPTION WHEN NO_DATA_FOUND THEN

    L_COUNT:=0;

    END;

    END;

  • Marwim
    Marwim Member Posts: 3,649 Gold Trophy
    edited Jul 13, 2015 3:32AM

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

    Regards

    Marcus

    ReemaPuri
  • ReemaPuri
    ReemaPuri Member Posts: 1,358 Gold Badge
    edited Jul 13, 2015 3:59AM

    thank you for pointing out

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

    SELECTnvl(max(column_name),0)   FROM T1

    where ID = input_id;

    this will handle null and no data found both

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond
    edited Jul 13, 2015 7:07AM

    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.

This discussion has been closed.