-
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
-
"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
-
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
-
thank you
Oracle Version: 9.2.0.1.0
OS Client & Server: Windows XP Pro
-
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.
-
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?
"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
-
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
-
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|