Forum Stats

  • 3,722,541 Users
  • 2,244,332 Discussions
  • 7,849,915 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

efficient way to check if record exists

944524
944524 Member Posts: 555
edited July 2015 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

Answers

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,903 Gold Crown
    edited July 2015

    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: 18,195 Black Diamond
    edited July 2015

    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 July 2015
    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 July 2015

    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: 40,186 Red Diamond
    edited July 2015

    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.

  • Prashant Dabral
    Prashant Dabral Member Posts: 858 Silver Badge
    edited July 2015

    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 July 2015

    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,620 Gold Trophy
    edited July 2015

    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 July 2015

    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: 18,195 Black Diamond
    edited July 2015

    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.

  • ReemaPuri
    ReemaPuri Member Posts: 1,358 Gold Badge
    edited July 2015

    is there any way to handle no_data_found in sql not plsql besides max

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

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,195 Black Diamond
    edited July 2015

    ANY aggregate function with IMPLICIT group by will not raise NO_DATA_FOUND, so


    SELECT  COUNT(*)

      FROM  table_name

      WHERE column_name = value

        AND rownum = 1;


    will not raise NO_DATA_FOUND.


    SY.

  • ReemaPuri
    ReemaPuri Member Posts: 1,358 Gold Badge
    edited July 2015

    YES thats true that will return 0.

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,097 Red Diamond
    edited July 2015
    944524 wrote:
    
    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
    
    

    The approach is not thread safe in normal programming speak - or multi-processing/multi-session/multi-transaction safe in database speak.

    At T1 process 1 deletes rows foo.

    At T2 your process does a select count and find that row foo exists.

    At T3 process 1 commits and row foo has gone to the big bit bucket in the sky.

    At T4 you process evaluates "flag > 0" predicate, finds it true, incorrectly assumes that row foo exists, and proceeds to process based on that erroneous assumption.

    The correct method is to force some kind of serialisation on row foo to ensure it exists when decisions are made to process business data, based on the fact that foo is actually there.

    This can be done by locking row foo (even if it is not updated), to ensure that the processing done based on foo, is done while foo actually does exist. So instead of a select count to test whether foo exists at that point in time, a select for update to lock foo, and ensure it does exist when "perform operations" happen.

    This is a critical concept to understand. I have seen (and still see) lots of code that ignores this. Yes, such code may well work fine 99% of the time. But when it does not, and processing occurs based on an erroneous assumption, data integrity is lost. And this can have a severe impact on the business, with dire financial implications, customers loosing their trust in the business, failed auditing, with lots more of other bad things.

    FatMartinR
  • 944524
    944524 Member Posts: 555
    edited July 2015

    can you clarify with an example to handle this i didn't get foo???

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,097 Red Diamond
    edited July 2015

    foo = any arbitrary row that the processes are looking for

  • Arun#
    Arun# Member Posts: 108
    edited July 2015

    Hi,

    I would go with Frank Kulash's existence check method with a slight modification to handle nulls. This query will check only for the existence of the record and will not count the total records : -

    SELECT  count(1)

    INTO    flag

    FROM    dual

    WHERE   EXISTS (

                       SELECT  1

                       FROM    t1

                       WHERE   id  = input_id

                   )

    ;

    Jonathan Lewis
  • Bawer
    Bawer Member Posts: 364
    edited July 2015

    just another way:

    for i in (select 1 from dual where dummy ='X' having count(*) > 0) loop

      --this block runs if the query returns a row, which means there are min. one row in the main query

         null;

    end loop;

  • Marwim
    Marwim Member Posts: 3,620 Gold Trophy
    edited July 2015
    Bawer wrote:
    
    just another way:
    
    for i in (select 1 from dual where dummy ='X' having count(*) > 0) loop
      --this block runs if the query returns a row, which means there are min. one row in the main query
         null;
    end loop;
    

    But you get exactly the concurrency problems described by Billy

  • Bawer
    Bawer Member Posts: 364
    edited July 2015

    you were right, if I want to check by more processes.

    If there is only one process which asks the table, no locking is needed. how efficient is it if you locks a table/row in a loop (assuming even there is only one loop)?

    it all depends on the business logic. What I wrote is only a part.

  • 944524
    944524 Member Posts: 555
    edited July 2015

    can you let me know the query how i can use in foo not able to get

  • Ghys42
    Ghys42 Member Posts: 18
    edited July 2015

    English follow

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,097 Red Diamond
    edited July 2015

    Instead of a "select count(*)" SQL and then an "update" SQL, you need to do a "select for update" SQL (prevents it from being deleted/changed), and then do the "update" SQL.

    Or simply do the "update" SQL directly - without checking if the row exists up front. After the update, check if a row was updated (using SQL%RowCount variable).

    If so, then success.. Commit.

    If not, then failure. Rollback and raise an application exception.

  • 944524
    944524 Member Posts: 555
    edited July 2015

    I don't want to perform the update or insert or proceed further if there is no record exist in the table

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,186 Red Diamond
    edited July 2015

    Hi,

    944524 wrote:
    
    I don't want to perform the update or insert or proceed further if there is no record exist in the table
    

    Then Billy's suggestion (reply #23) is perfect for you.  Simply do an UPDATE statement.  If no such row exists, then nothing will be changed.

This discussion has been closed.