Re: count(*) ?

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Thu, 17 Jan 2008 12:03:28 +0100
Message-ID: <ee2e3$478f3600$524b5c40$17910@cache2.tilbu1.nb.home.nl>

shakespeare wrote:
> "Frank van Bortel" <frank.van.bortel_at_gmail.com> schreef in bericht
> news:ee096$478f27b0$524b5c40$17089_at_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 up

>
> Sorry, that's what I meant too, but I wrote "select" where I should have
> written "count".
> And about hanky panky: it's nullable columns that make the difference in
> count(column) and count(*) (different speed, different results).
> The "myth" article is about count(1), count(*) and count (rowid) and things
> like that.
>
> Shakespeare
>
> Shakespeare
>
>

As well as Primary Key columns - bottom line: it does not matter.
http://asktom.oracle.com/pls/asktom/f?p=100:11:3615348475281792::::P11_QUESTION_ID:1156159920245

-- 

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

Received on Thu Jan 17 2008 - 05:03:28 CST