This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Nov 21, 2012 10:30 AM by 942572 RSS

Fastest way to  check if a record exists

942572 Newbie
Currently Being Moderated
Hi,

I have a table with more than 100M records. What is the fastest way to check if a record exists or not. Currently, I am using Oracle 11.2. The following is the SQL I used, id is the primary key of the table_test.

select count(*) into existFlag
from table_test
where id = testID
;
  • 1. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    select count(*) into existFlag
    from table_test
    where id = testID
    and rownum = 1
    /
    SY.
  • 2. Re: Fastest way to  check if a record exists
    942572 Newbie
    Currently Being Moderated
    Sorry, it does NOT make too much difference.
  • 3. Re: Fastest way to  check if a record exists
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    939569 wrote:
    Sorry, it does NOT make too much difference.
    No, it shouldn't. The query you started with is about the best you can do; nothing is going to be much faster.

    If you're doing the query you posted just once, it will be hard to tell if it's fast or not. If you're doing it over and over again for some reason, then there may be a faster way to do whatever you need to do. Give us a bigger picture. Describe exactly what you're doing, and we'll see if there's a better way to do it.

    Edited by: Frank Kulash on Nov 20, 2012 12:26 PM
  • 4. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    Is column id indexed?

    SY.
  • 5. Re: Fastest way to  check if a record exists
    942572 Newbie
    Currently Being Moderated
    Yes, it is the primary key and has the unique index on this column.
  • 6. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    939569 wrote:
    Yes, it is the primary key and has the unique index on this column.
    Then ROWNUM=1 makes no difference and it is as fast as it gets (assuming it does use index - check execution plan).

    SY.
  • 7. Re: Fastest way to  check if a record exists
    6363 Guru
    Currently Being Moderated
    939569 wrote:

    I have a table with more than 100M records. What is the fastest way to check if a record exists or not.
    Don't check if it exists

    http://tkyte.blogspot.com/2008/12/doing-it-wrong.html

    >
    Why bother counting - and then processing if that count was greater than zero. Why not just process_the_data
  • 8. Re: Fastest way to  check if a record exists
    942572 Newbie
    Currently Being Moderated
    Here is the big picture. I need to do such query for about 10K times, each time there will be a different ID. Since there are different complex operations base on whether the record exists or not, I am not able to use bulk sql. I have to loop 10K times, query the exists of t he record and then process different operations.

    Any suggestion? Thanks!
  • 9. Re: Fastest way to  check if a record exists
    user346369 Expert
    Currently Being Moderated
    I need to do such query for about 10K times
    Big picture doesn't explain well enough. With those "10K times", what do you do if the record exists? Are you updating that record?

    If the record does not exist, what does the process do?

    I have a hunch you can process without the initial "check if a record exists", but without more than the big picture, it's hard to say.
  • 10. Re: Fastest way to  check if a record exists
    6363 Guru
    Currently Being Moderated
    939569 wrote:

    Since there are different complex operations base on whether the record exists or not, I am not able to use bulk sql.
    What functions or operators are you using that are not available in SQL?
    I have to loop 10K times, query the exists of t he record and then process different operations.
    If you absolutely have to execute count(*) in a loop, I would suggest getting used to the fact that this processing is going to be slow and focus on explaining that some things just take a long time to do, and that this is one of them.
  • 11. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    939569 wrote:
    Any suggestion?
    SQL> create or replace
      2    type NumList
      3      as table of number
      4  /
    
    Type created.
    
    SQL> set serveroutput on
    SQL> declare
      2      v_check_list         NumList := NumList(7521,1,7566,7654,99,7698,7782);
      3      v_exists_list        NumList;
      4      v_does_not_exit_list NumList;
      5  begin
      6      select  empno
      7        bulk  collect
      8        into  v_exists_list
      9        from  emp
     10        where empno member of v_check_list;
     11      v_does_not_exit_list := v_check_list multiset except v_exists_list;
     12      for v_i in 1..v_exists_list.count loop
     13        dbms_output.put_line('EMPNO ' || v_exists_list(v_i) || ' exists.');
     14      end loop;
     15      for v_i in 1..v_does_not_exit_list.count loop
     16        dbms_output.put_line('EMPNO ' || v_does_not_exit_list(v_i) || ' does not exist.');
     17      end loop;
     18  end;
     19  /
    EMPNO 7521 exists.
    EMPNO 7566 exists.
    EMPNO 7654 exists.
    EMPNO 7698 exists.
    EMPNO 7782 exists.
    EMPNO 1 does not exist.
    EMPNO 99 does not exist.
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    So just substitute dbms_output with complex operations based on whether the record exists or not. And MEMEBER OF is more time consuming than:
    SQL> declare
      2      v_check_list         NumList := NumList(7521,1,7566,7654,99,7698,7782);
      3      v_exists_list        NumList;
      4      v_does_not_exit_list NumList;
      5  begin
      6      select  empno
      7        bulk  collect
      8        into  v_exists_list
      9        from  emp
     10        where empno in (select * from table(v_check_list));
     11      v_does_not_exit_list := v_check_list multiset except v_exists_list;
     12      for v_i in 1..v_exists_list.count loop
     13        dbms_output.put_line('EMPNO ' || v_exists_list(v_i) || ' exists.');
     14      end loop;
     15      for v_i in 1..v_does_not_exit_list.count loop
     16        dbms_output.put_line('EMPNO ' || v_does_not_exit_list(v_i) || ' does not exist.');
     17      end loop;
     18  end;
     19  /
    EMPNO 7521 exists.
    EMPNO 7566 exists.
    EMPNO 7654 exists.
    EMPNO 7698 exists.
    EMPNO 7782 exists.
    EMPNO 1 does not exist.
    EMPNO 99 does not exist.
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SY.
  • 12. Re: Fastest way to  check if a record exists
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    939569 wrote:
    Here is the big picture. I need to do such query for about 10K times, each time there will be a different ID. Since there are different complex operations base on whether the record exists or not, I am not able to use bulk sql. I have to loop 10K times, query the exists of t he record and then process different operations.

    Any suggestion? Thanks!
    It sounds like you should put the 10k target ids in a separate table (perhaps a global temporary table), then join it to the main table.
    How are you getting the 10k target ids? Are they the result set of some other query? If so, you already have the ids in a separate table; you just need to join the results of that other query to your 100M-row table.

    Post an example of what you want to do. Include CREATE TABLE and INSERT statements for the relevant columns of your 100M-row table. 5 or 10 rows of sample data will probably be plenty.
    Give an example of what you need to do. Instead of posting 10,000 target ids, give 2 or 3 target ids and the results you want, given the sample data you posted. We understand how big your real tables are, and we'll find an appropriate solution.
  • 13. Re: Fastest way to  check if a record exists
    Solomon Yakobson Guru
    Currently Being Moderated
    BTW, you don't have to use nested tables. You could use GTT, for example:
    SQL> create global temporary table check_list(id number)
      2  /
    
    Table created.
    
    SQL> insert
      2    into check_list
      3    values(7521)
      4  /
    
    1 row created.
    
    SQL> insert
      2    into check_list
      3    values(1)
      4  /
    
    1 row created.
    
    SQL> insert
      2    into check_list
      3    values(7566)
      4  /
    
    1 row created.
    
    SQL> insert
      2    into check_list
      3    values(7654)
      4  /
    
    1 row created.
    
    SQL> insert
      2    into check_list
      3    values(99)
      4  /
    
    1 row created.
    
    SQL> insert
      2    into check_list
      3    values(7698)
      4  /
    
    1 row created.
    
    SQL> insert
      2    into check_list
      3    values(7782)
      4  /
    
    1 row created.
    
    SQL> begin
      2      for v_rec in (select empno,id from emp,check_list where empno(+) = id) loop
      3        if v_rec.empno is not null
      4          then
      5            dbms_output.put_line('EMPNO ' || v_rec.id || ' exists.');
      6          else
      7            dbms_output.put_line('EMPNO ' || v_rec.id || ' does not exist.');
      8        end if;
      9      end loop;
     10  end;
     11  /
    EMPNO 7521 exists.
    EMPNO 1 does not exist.
    EMPNO 7566 exists.
    EMPNO 7654 exists.
    EMPNO 99 does not exist.
    EMPNO 7698 exists.
    EMPNO 7782 exists.
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SY.
  • 14. Re: Fastest way to  check if a record exists
    942572 Newbie
    Currently Being Moderated
    Thanks for all the suggestion. It seems I have two options here:

    1. Don't use the count(*) within the loop. I just cleaned up the logic of the code as following:
    If record_exist
    Then
    copy_data_from_baseTable_to_historyTable
    update_data_to_baseTable
    Else
    insert_data_to_baseTable
    I don't know how to use the merge statement to execute both copy and update actions when the record does exist. Any suggestion on merge or other way to check the existing of the record without count(*)?

    2. Don't use loop, but use GTT and join statement. I am going to try it out and update you later.

    Cheers,
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points