Re: What to Count - WAS Re: Newbie ? - Howto find nbr records in a table

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Wed, 07 Oct 1998 13:46:15 -0700
Message-ID: <361BD316.B7458F26@uclink4.berkeley.edu>

Hey Allan,

     You could also use Count(1), Count(3), Count(1974) or Count('jay').

     I don't have a *really* large table to test the timing against, but I cannot imagine the difference to be too great. My co-worker, Ken Geis, just ran the following against a table with about 43,000 records and count(*) was about 2/100th of a second faster then count(9). I agree with your point: if you can type Count(9) noticeably faster then COUNT(*), you could make your savings there.

DECLARE
  time1 NUMBER;
  time2 NUMBER;
  this_count NUMBER;
BEGIN
  time1 := dbms_utility.get_time;
    SELECT count(*)

      INTO this_count
      FROM foo;

  time2 := dbms_utility.get_time;

  dbms_output.put_line(time2 - time1);
  dbms_output.put_line(this_count);

  time1 := dbms_utility.get_time;
    SELECT count(*)

      INTO this_count
      FROM foo;

  time2 := dbms_utility.get_time;

  dbms_output.put_line(time2 - time1);
  dbms_output.put_line(this_count);
END; Jay!!!

"Alan D. Mills" wrote:

> This raises a question for me. The use of COUNT(*) that is. A few years
> ago, in my early days of Oracle, a chap with more experience than I
> explained that he always used COUNT(9) as opposed to COUNT(*) when doing
> this sort of thing. Obviously, it makes absolutley no difference to the
> answer. Let's face it, it doesn't matter what you count, the value for each
> record is still one as we'd like.
>
> This chap explained is odd use of COUNT(9) away by explaining that using *
> will retrieve all data from each record, just as with SELECT *, you get all
> columns back. Using COUNT(9) you only have to locate the record and not
> extract anything from it so it should be supposedly a little quicker. It
> makes a sort of logical sense and I do use COUNT(9) sometimes. Is there
> actually any foundation to this argument though? I've certainly never been
> able to prove it with any timings. Maybe it simply comes down to whatever
> is easiest tp type for individuals?
>
> Comments?
> --
> Alan D. Mills
>
> Jason Jay Weiland wrote in message
> <361A6526.3B04961F_at_uclink4.berkeley.edu>...
> >Brian,
> >
> >Try this:
> >
> >SELECT COUNT(*) FROM FOO;
> >
> >...where FOO is the table name.
> >
Received on Wed Oct 07 1998 - 15:46:15 CDT