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:

Quote:

---

select count(*) into v_count from test_abc_prices;

---

then i'm checking whether count(*) >0:

Quote:

---

if (v_count > 0) then null

---

Is there a more efficient way?

oops

Code:

---

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

---

should be faster

thanks

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.

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

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?

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 :)

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

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. ;)

I can't help but notice that line 14 appears to have a small but significant cost. I wonder what happens on that line?

Quote:

---

Originally Posted by padders

I can't help but notice that line 14 appears to have a small but significant cost. I wonder what happens on that line?

---

That small but insignificant cost is the fetch into the explicit cursor. :o
But for most systems all three methods are equally valid, IMHO. ;)

Thanks

Line 46 (select) is faster than line 30 (select count(*)).
This is what I expected.

Tamil

I'd still maintain that, in most cases, select count(*) is more efficient in human terms - less coding and somewhat easier to understand when you return to it two years hence - unless you spend half a screen explaining why you need that cursor!

The results prove that they are equal.

Code:

---

use_explicit_cursor returned TRUE!!!
use_explicit_cursor Time (seconds) 0
use_select_into returned TRUE!!!
use_select_into_1 Time (seconds) 0
use_select_into returned TRUE!!!
use_select_into_2 Time (seconds) 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

---

Quote:

---

Elapsed: 00:00:00.00

---

Haven't we learned before that elapsed time means nothing? The trace matters.