Systems Engineering and RDBMS

Archive for July 29th, 2007

No difference between Count(*) and Count(1)

Posted by decipherinfosys on July 29, 2007

Some folks at client sites have asked this question so I am posting it. For Oracle DBA’s who worked in version 7.x and before, they are still under the opinion that there is a difference between count(*) and count(1). In those old releases, there used to be a performance difference between the two (count(1) used to be slower). Count(1) and Count(*) are essentially the same thing. There is absolutely no difference between them…they do the same thing, take the same amount of resources and return the same output. You can very easily do a trace and run tkprof to see the number of blocks read or written or processed and compare the CPU times and the elapsed times and they will be identical. You can also see examples for those runs (including an example of a count(cola) where cola could be a not null indexed column vs a non-indexed column) over here:

In addition, let me mention one more thing which I have seen some developers do – they typically use a query like “select count(*) from table_foo where x= :1” sometimes to just find out whether there are any qualifying records in the table for that criteria and then based on the non-zero or zero value returned, the code takes the respective routes. Instead of doing that, you can just do: “select /*+ first_rows_1 */ col1 from table_foo where x = :1 and rownum < 2”. This is so that if there are a lot of records that qualify for that criteria, then you do not have incur the cost of aggregate operation since all that you are interested in is to find out whether any record exists at all. Likewise, you can use the TOP 1 clause in SQL Server and the FETCH FIRST N ROWS clause in DB2 LUW to do the same thing.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »