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 betterThis discussion is not solely related to COUNT, but advanced usage of the indexes in general.
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.
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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
