浅析count(1) count(*) count(col)的区别和性能

来源：互联网发布：笔记本硬盘的数据导出编辑：程序博客网时间：2024/05/16 09:10

count(1)和count(*)没有任何区别(执行计划和统计信息)，而且都是统计所有行
count(col)统计col列不为空的记录，如果有索引，不管col是否为空，都能走索引，没有索引就无法走
测试

         hr@ORCL> select count(1) from t;                    Execution Plan          ----------------------------------------------------------          Plan hash value: 2966233522                    -------------------------------------------------------------------          | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |          -------------------------------------------------------------------          |   0 | SELECT STATEMENT   |      |     1 |   159   (2)| 00:00:02 |          |   1 |  SORT AGGREGATE    |      |     1 |            |          |          |   2 |   TABLE ACCESS FULL| T    | 50356 |   159   (2)| 00:00:02 |          -------------------------------------------------------------------                    hr@ORCL> select count(*) from t;                    Execution Plan          ----------------------------------------------------------          Plan hash value: 2966233522                    -------------------------------------------------------------------          | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |          -------------------------------------------------------------------          |   0 | SELECT STATEMENT   |      |     1 |   159   (2)| 00:00:02 |          |   1 |  SORT AGGREGATE    |      |     1 |            |          |          |   2 |   TABLE ACCESS FULL| T    | 50356 |   159   (2)| 00:00:02 |          -------------------------------------------------------------------