As a matter of fact, the table occupies1265649 data blocks with a block fill
rate of 98%.
On the other hand, the BITMAP index used by the select count(*) occupies 932
blocks.
Maybe you should refrain from stating the obvious until you have all the
facts.
Jurij Modic wrote in message <3547b016.10922560_at_www.sigov.si>...
>On Tue, 28 Apr 1998 14:11:43 -0600, bialik_at_wis.weizmann.ac.il wrote:
>
>>Hi.
>>
>>I run a number of test concerning the performance of COUNT(*) and a number
>>of different options.
>>
>>1. The bottom line : COUNT(*) is the best way to get the number of rows in
>> the table.
>>2. It is possible to force INDEX SCAN by using PK-fld > 0 in WHERE clause,
>> but the performance suffers.
>>I think that the main reason for bad performance is an execution of
>>SORT(AGGREGATE).
>>
>>Following the results of test ( starting with worst case and going to the
>>best one ).
>> ...[SNIP]...
>
>From your trace results it's obvious that your index occupies more
>database blocks than your table (i.e. the rowids in the index take
>more space than the non-indexed columns in the table). Apparently your
>index occupies aproximately 42.000 blocks while you table fills only
>around 40.000 blocks. So it is naturaly that in your case index scan
>will take more time than table scan. In praxis however individual
>indexes usualy take much less space than their underlying tables and
>their full scans can be much quicker than table full scans.
>
>What surprises me is the fact that COUNT(*) is realy faster than
>COUNT(anything_else) - I tested that on some of my test tables and
>COUNT(*) was approximately 10% faster. I allways thought it is the
>other way around, because I assumed that with COUNT(*) the parser will
>have some extra work identifying all the table columns from the
>dictionary. Can anyone explain where does this execution time
>difference between COUNT(*) and COUNT(anything_else) come from?
>
>Regards,
>============================================================
>Jurij Modic Republic of Slovenia
>jurij.modic_at_mf.sigov.mail.si Ministry of Finance
>============================================================
>The above opinions are mine and do not represent any official
>standpoints of my employer
Received on Thu Apr 30 1998 - 17:20:52 CDT
