Re: How can I count rows fast ?

From: Michael Sallwasser <msallwas_at_world.nad.northrop.com>
Date: Mon, 20 Jun 1994 19:38:52 GMT
Message-ID: <Crpnwu.E80_at_gremlin.nrtc.northrop.com>


In article <CrCvJ4.FGz_at_everestech.com> matthews_at_everestech.com (Michael C. Matthews) writes:
>In article <Cr6nq2.Iv3_at_nl.oracle.com> cgohring_at_uk.oracle.com writes:
>>derwand_at_sap-ag.de (Guido Derwand) writes:
>>: Is there a faster method to count rows in a table than using SQL
>>: 'SELECT COUNT(*)'
>>
>>Rather than doing a count(*), do a count(<column>).
>>where <column> is a column with an index on it.
>>This can be resolved with the index alone, then, and prevent a full table
>>scan.
>>
>Of course, this will only work if that column has no NULLs in it.
>count(<column>) gives you a count of non-NULL values in that column.
>count(*) gives you a count of ALL rows.




ALERT! ALERT! ALERT! ALERT! ALERT! ALERT! ALERT! ALERT! ALERT! ALERT! ALERT! ALERT! ALERT! ALERT!                 I have heard this repeated by MANY people, including Oracle employees; *BUT* if one does an EXPLAIN PLAN, Oracle V6 indicates it will perform a

     >>>>>>>> FULL TABLE SCAN <<<<<<<  

Does anyone have experience to the contrary? I have empirical evidence that it actually takes longer(!?) when you specify the column, EVEN when the column is designated NOT NULL.



-- 
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down 
Northrop Grumman    | the other is total destruction. Let us choose wisely.
============================================================================
Received on Mon Jun 20 1994 - 21:38:52 CEST

Original text of this message