(转自puber yong huang版主)

Controversy about count(*), count(1), count(PK) and count(rowid)
Every 1 out of 100 people using Oracle believes using count(*) is slower than either count( ), count() or count(rowid); 1 out of 1000 believes count(*) is faster than any other (ratio based on personal impression!). Naive reasoning is that count(*) undergoes a full table scan implied by the *, which means all columns in select * from mytable. "Sophisticated" reasoning is that Oracle internally optimizes the SQL statement when you use count(*). This topic has become an FAQ on several Oracle discussion forums and to some, also an annoying dead horse.

But interestingly, Guy Harrison had some research in this subject in his famous Oracle SQL High-Performance Tuning (see pages 204-5). His execution plan in tkprof indicates that count(*) gives a SORT AGGREGATE on 0 rows (i.e. no sort aggregate), while count(0) and count(customer_id) both show SORT AGGREGATE on 5151 rows. Several people and I on an Oracle mailing list performed the same test on both Oracle 8i and 7.3. Nobody has reproduced SORT AGGREGATE on 0 rows. I emailed Guy Harrison about our test but never received any response.

Then another person performed a test checking the statistic "session logical reads" for all these count methods. The number due to all these counts turned out to be exactly the same.

Can we announce this one count faster than others another urban legend?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-350569/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-350569/