Re: How can I count rows fast ?

In article 32246_at_uswnvg.uswnvg.com, kimmng_at_uswnvg.com (Kim Ng) writes:
>I do not understand how a computer can count something without going through
>the whole table (unless, of course, if there is a counter somewhere that is
>being kept --- I probably won't trust that counter anyway --- or you only
>want to count records that match a certain criteria).
>
>Logically, it will have to go through the whole table from top to bottom
>(like a person counting how many fingers he/she have). Any other way would
>be just guessing.
>
>I would love to hear if there is a short cut. I am sure billions of others
>would too.
>
>-----------------------------
>Kim Ng
>(Just a low life contract programmer. Thus, my clients won't adopt my views.)

Counting a unique, non-null column can be MUCH faster than a full table scan.

I haven't done a tkprof, but the counts I've done on some very large tables were considerably faster than full table scans on those same tables. So I assume that Oracle is smart enough to use such an index - or might be persuaded to.

Sincerely,

Martin Farber
Independent Oracle Consultant

                        |  NYNEX Science & Technology
                        |  500 Westchester Ave, Rm 1D10
                        |  White Plains, NY  10604
                        |     -----------------
                        |  email: farber_at_nynexst.com
                        |  Voice: 914/644-2656
                        |    FAX: 914/644-2216