Best Way to check that table is empty

[buck]

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?

[DaPi]

oops

[DaPi]

select count(*) into v_count from test_abc_prices
where rownum = 1;

should be faster

[buck]

thanks

[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.

[tamilselvan]

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.

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

[DaPi]

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?

[DaPi]

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

[gandolf989]

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!!!!!!!!!

[gandolf989]

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.