Re: The use of COUNT(*)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 10 Jul 1998 16:29:22 GMT
Message-ID: <35ad408c.12908581@192.86.155.100>

A copy of this was sent to "Lai King Leung" <LaiKing_at_syslogic.com> (if that email address didn't require changing) On Fri, 10 Jul 1998 08:43:59 GMT, you wrote:

>Could anyone tell me about the use of COUNT(*)? I currently use the
>comamnd
>
>SELECT COUNT(*) INTO v_count FROM tbl_data WHERE ... in my PL/SQL program
>unit, to determine whether there are any records to be processed.
>
>In some cases I use the command
>SELECT COUNT(*) INTO v_count FROM tbl_tmpdata to determine if there is any
>data in the table.
>
>I've been told that it is very inefficient to use COUNT(*) and that I
>should declare a CURSOR and look at the %FOUND attribute instead ??
>

count(*) will count all of the records -- if you need only to know that at least one record exists, the most efficient method of coding that is:

declare

    ...
   cursor c1 is select count(*) from dual

                where exists ( select null from your_table where .... );

   l_cnt number;
   ....
begin

   open c1;
   fetch c1 into l_cnt;
   close c1;

   if ( l_cnt > 0 ) then ..... (row(s) exists) end;

the exists stops evaluating the subquery as soon as the first matching row is found.

that may be abbreviated as:

begin

    select count(*) into l_cnt from dual       where exists ( select null from your_table where ... );     if ( l_cnt > 0 ) then .....

but that will always do 2 fetches instead of just one to make sure the select .. into ... only returns one row.

>I would appreciate any suggestions on the use of COUNT(*) especially when
>it should be used, and when it should be avoided at all cost.
>
>Thank-you in adavnce
>Regards
>Lai King
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jul 10 1998 - 11:29:22 CDT