Re: count(*) ?
From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Thu, 17 Jan 2008 11:02:24 +0100
Message-ID: <ee096$478f27b0$524b5c40$17089@cache3.tilbu1.nb.home.nl>
>
> O yes it is! If you have a sparse column (say col_a), indexed in a very
> large table, count(col_a) will use the index , where count(*) doesn't (and
> they will return different values as well).
> I think you confused this with select (*) and select (1) (which perform the
> same, although you might find a DBA at your current working place who is
> convinced that count (8) is faster.....). They ARE not the same though, for
> count(*) from table in a view will not invalidate a view when a column is
> added to the table, where count(1) will.....
>
> Shakespeare
>
>
No I was not confused, though have to admit I misread the column name bit.
The title had the start of another count(1) (or count(42), for that matter) is better (or worse) that count(*).
Date: Thu, 17 Jan 2008 11:02:24 +0100
Message-ID: <ee096$478f27b0$524b5c40$17089@cache3.tilbu1.nb.home.nl>
shakespeare wrote:
> "Frank van Bortel" <frank.van.bortel_at_gmail.com> schreef in bericht
> news:7767f$478cff47$524b5c40$12171_at_cache5.tilbu1.nb.home.nl...
>> nick wrote: >>> I understand that when you feed the count( ) function an asterisk as >>> an argument it runs >>> slower than if you use a column name as an argument. Can someone tell >>> me why this is so? >>> >>> Thanks >> Test it - it is not so - who do you believe?!? >> >> -- >> >> Regards, >> Frank van Bortel >> >> Top-posting in UseNet newsgroups is one way to shut me up
>
> O yes it is! If you have a sparse column (say col_a), indexed in a very
> large table, count(col_a) will use the index , where count(*) doesn't (and
> they will return different values as well).
> I think you confused this with select (*) and select (1) (which perform the
> same, although you might find a DBA at your current working place who is
> convinced that count (8) is faster.....). They ARE not the same though, for
> count(*) from table in a view will not invalidate a view when a column is
> added to the table, where count(1) will.....
>
> Shakespeare
>
>
No I was not confused, though have to admit I misread the column name bit.
The title had the start of another count(1) (or count(42), for that matter) is better (or worse) that count(*).
That myth I wanted to stop right here.
As far as the count(col) vs count(*) myth: http://www.oracledba.co.uk/tips/count_speed.htm
Of course, assuming you want to know how many entries your table has (so not hanky-panky with nullable columns!)
-- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me upReceived on Thu Jan 17 2008 - 04:02:24 CST