浅析count(1) count(*) count(col)的区别和性能
url: http://blog.csdn.net/dba_waterbin/article/details/8569257
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 |
- -------------------------------------------------------------------