Best Way to check that table is empty
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Best Way to check that table is empty

Hybrid View

  1. #1
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28

    Best Way to check that table is empty

    Hi,

    what is the best way to check that a table is empty?

    I have procedure with the following statement:
    select count(*) into v_count from test_abc_prices;
    then i'm checking whether count(*) >0:

    if (v_count > 0) then null
    Is there a more efficient way?
    Oracle Version: 9.2.0.1.0
    OS Client & Server: Windows XP Pro

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    oops
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Code:
    select count(*) into v_count from test_abc_prices
    where rownum = 1;
    should be faster
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28

    Thumbs up thank you

    thanks
    Oracle Version: 9.2.0.1.0
    OS Client & Server: Windows XP Pro

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Create a function with an explicit cursor. Open the cursor and return one row. Check to see if you got data, if you did return TRUE, otherwise return FALSE. This is a method mentioned by Steven Feuerstein's book PL/SQL Best Practices.
    this space intentionally left blank

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Quote Originally Posted by gandolf989
    Create a function with an explicit cursor. Open the cursor and return one row. Check to see if you got data, if you did return TRUE, otherwise return FALSE. This is a method mentioned by Steven Feuerstein's book PL/SQL Best Practices.
    I don't think I'd subscribe to this as "best practice" - it is taking a sledge-hammer to a nut. And Tamil will find it much too slow
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Quote Originally Posted by DaPi
    I don't think I'd subscribe to this as "best practice" - it is taking a sledge-hammer to a nut. And Tamil will find it much too slow
    I ran this on a table that has over 5 million rows and could see no difference bewteen the three execution plans. They each ran in about 0 seconds. But I still wasn't convinced, so I ran the above with DBMS_PROFILER, and found out that the explicit cursor is some fraction of a millisecond faster than the select into. Who would have thunk it!!!!

    So in some small and inconsequential way, I was right!!!!!!!!!
    Attached Images Attached Images
    Attached Files Attached Files
    this space intentionally left blank

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by DaPi
    Code:
    select count(*) into v_count from test_abc_prices
    where rownum = 1;
    should be faster

    Why do you need count(*) if you want only one row?

    [/CODE]

    This is slower to me.

    Code:
    SQL> set time on timing on
    14:56:34 SQL> create table empty_table (id int) ;
    
    Table created.
    
    Elapsed: 00:00:00.04
    14:56:47 SQL> select count(*) from empty_table where rownum = 1 ;
    
      COUNT(*)
    ----------
             0
    
    Elapsed: 00:00:00.01 - one hundredth of a second. 
    
    14:57:12 SQL> select 1 from empty_table where rownum = 1 ;
    
    no rows selected
    
    Elapsed: 00:00:00.00  -- almost negilible 
    14:57:31 SQL>
    Tamil

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi Tamil,

    The OP is doing this in a PL/SQL environment - the count(*) simplifies coding a bit, which might be worth the odd cs. Otherwise he has to handle exceptions. How much does exception handling cost?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Line 8 in the jpg relating to the explicit cursor in the code. I bet if I ran this test 1 million times the difference might even be a few seconds.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width