如果列是非空的话,count(*)与count(列)是基本一样的,有人说count(列)的性能会好一点,但我测试了一下,发现其实也不一定,有时count(*)的查询时间反而会小一点.
如果列含有空值,那count(列)就只计算非空值,count(*)其实是计算有多少行(包括列为空的行),所以结果就不一样了.
如:
SQL> WITH TEST AS (SELECT 1 ID FROM DUAL
2 UNION ALL SELECT 3 FROM DUAL
3 UNION ALL SELECT 4 FROM DUAL
4 UNION ALL SELECT NULL FROM DUAL
5 UNION ALL SELECT 5 FROM DUAL
6 UNION ALL SELECT NULL FROM DUAL
7 union all select null from dual)
8 SELECT count(*) FROM TEST;
COUNT(*)
----------
7
SQL> WITH TEST AS (SELECT 1 ID FROM DUAL
2 UNION ALL SELECT 3 FROM DUAL
3 UNION ALL SELECT 4 FROM DUAL
4 UNION ALL SELECT NULL FROM DUAL
5 UNION ALL SELECT 5 FROM DUAL
6 UNION ALL SELECT NULL FROM DUAL
7 union all select null from dual)
8 SELECT count(id) FROM TEST;
COUNT(ID)
----------
4
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13387766/viewspace-517410/ ，如需转载，请注明出处，否则将追究法律责任。
