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

I've been following this thread. In the old days I was told to always use count(rowid). Any thoughts?

Van

martinj (replace this with @) xs4all.nl wrote in message <361d116b.7908241_at_news.xs4all.nl>...
>Alan,
>
>>I like this idea. If my primary key is a compound key (i.e. more than one
>>column) should I just choose any one or aggregate (concatenate) them to a
>>single value.
>
>If you aggregate the values to a single value, your index will not be
>used anymore ;-)
>
>Suppose the index is built up with the fields A, B and C with the
>corresponding values '0000', 'XXXX' and '9999'. If you've created the
>index on the three fields, the index will look like this:
>'0000XXXX9999'. Considering this, I think it doesn't matter if you
>query with one field or all three.
>
>If you have another non unique index on a field that doesn't contain
>NULL's (because they're not indexed!), you can also use this index.
>You will benefit from this index if it's keys are smaller than the
>other index and certainly if the indextree is 'flatter'. In the latter
>case, the indextree doesn't have much leaves which results in less
>reading.
>
>>I'm not sure this idea will always work though as I find that when
counting
>>it's usually grouping by some combination which you can;t all get fromt he
>>same index so you're back to hitting the table again.
>
>If you're grouping on a non-indexed field, you will better have a full
>table scan. The index will only create overhead.
>
>>From the rest of your answer though, it sounds like COUNT(*) is quicker
than
>>COUNT(9). In the former, no work at all is done, in the latter the
>>expression '9' must be evaulated. Have I got this right?
>
>I suppose so. I don't have an answer to that. An Oracle performance
>tuning book states: "We tested the following statements on several
>different computers and found that count(*) consistently runs between
>15% and 20% faster than count(1), and that count(index_column) is 5%
>faster again. The following runs counted 65,536 rows from a table:
>select count(unique index column) from transactions : 2.43 seconds
>select count(*) from transaction : 2.59 seconds
>select count(1) from transaction : 3.47 seconds".
>
>Will this do?
>---------------------------------------------------------------------------