D'Arcy J.M. Cain wrote:
>I'm not sure why everyone wants to push this into the database anyway.
>If I need to know the count of something, I am probably in a better
>position to decide what and how than the database can ever do. For
>example, I recently had to track balances for certificates in a database
>with 25M certificates with multiple transactions on each. In this case
>it is a SUM() instead of a count but the idea is the same. We switched
>from the deprecated money type to numeric and the calculations started
>taking too long for our purposes. We created a new table to track
>balances and created rules to keep it updated. All the complexity and
>extra work is limited to changes to that one table and does exactly what
>we need it to do. It even deals with transactions that get cancelled
>but remain in the table.
>
>If you need the count of entire tables, a simple rule on insert and
>delete can manage that for you. A slightly more complicated set of
>rules can keep counts based on the value of some field, just like we did
>for the certificate ID in the transactions. Getting the database to
>magically track this based on arbitrary business rules is guaranteed to
>be complex and still not handle everyone's requirements.
>
>
>
This discussion is not solely related to COUNT, but advanced usage of
the indexes in general.
Did everyone get to read the info on Oracle's fast full index scan? It
performs sequential I/O on the indexes, pulling all of the index blocks
into memory to reduce random I/O to speed up the index scan.