(转自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?
Every 1 out of 100 people using Oracle believes using count(*) is slower than either count(), count(
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博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-350569/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
