1 2 Previous Next 25 Replies Latest reply on Jul 19, 2015 8:07 PM by Frank Kulash

    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

        • 1. Re: efficient way to check if record exists
          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

          • 2. Re: efficient way to check if record exists
            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.

            • 3. Re: efficient way to check if record exists
              rp0428

              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.

              • 4. Re: efficient way to check if record exists
                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;

                • 5. Re: efficient way to check if record exists
                  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.

                  • 6. Re: efficient way to check if record exists
                    Prashant Dabral

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

                    • 7. Re: efficient way to check if record exists
                      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

                       

                      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;

                      • 8. Re: efficient way to check if record exists
                        Marwim

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

                         

                        Regards

                        Marcus

                        • 9. Re: efficient way to check if record exists
                          ReemaPuri

                          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

                          • 10. Re: efficient way to check if record exists
                            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.

                            • 11. Re: efficient way to check if record exists
                              ReemaPuri

                              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;

                               

                               

                              • 12. Re: Re: efficient way to check if record exists
                                Solomon Yakobson

                                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.

                                • 13. Re: efficient way to check if record exists
                                  ReemaPuri

                                  YES thats true that will return 0.

                                  • 14. Re: efficient way to check if record exists
                                    Billy~Verreynne

                                    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.

                                    1 2 Previous Next