count(*)、count(val)和count(1)的解释_count(*) value-CSDN博客 (2024)

一、关于count的一些谣言：

1、count(*)比count(val)更慢！项目组必须用count(val)，不准用count(*)，谁用扣谁钱！

2、count(*)用不到索引，count(val)才能用到。

3、count(*)是统计出全表的记录，是吞吐量的操作，肯定用不到索引。

4、count(1)比count(*)的速度快。

二、验证count(*)和count(val)

1、首先创建一个表，使用count(*)和count(val)查询比较：

<span style="font-size:14px;">----删除echo表----SQL> drop table echo purge;drop table echo purge *第 1 行出现错误:ORA-00942: 表或视图不存在----创建一张echo的测试表----SQL> create table echo as select * from dba_objects;表已创建。SQL> update echo set object_id = rownum;已更新72509行。SQL> commit;提交完成。SQL> set timing onSQL> set linesize 100SQL> set autotrace onSQL> select count(*) from echo; COUNT(*)----------已用时间: 00: 00: 00.01执行计划----------------------------------------------------------Plan hash value: 99109176-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| ECHO | 80064 | 290 (1)| 00:00:04 |-------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processedSQL> select count(*) from echo; COUNT(*)----------已用时间: 00: 00: 00.01执行计划----------------------------------------------------------Plan hash value: 99109176-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| ECHO | 80064 | 290 (1)| 00:00:04 |-------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processedSQL> select count(object_id) from echo;COUNT(OBJECT_ID)----------------已用时间: 00: 00: 00.01执行计划----------------------------------------------------------Plan hash value: 99109176---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 290 (1)| 00:00:04 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | TABLE ACCESS FULL| ECHO | 80064 | 1016K| 290 (1)| 00:00:04 |---------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processedSQL> select count(object_id) from echo;COUNT(OBJECT_ID)----------------已用时间: 00: 00: 00.01执行计划----------------------------------------------------------Plan hash value: 99109176---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 290 (1)| 00:00:04 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | TABLE ACCESS FULL| ECHO | 80064 | 1016K| 290 (1)| 00:00:04 |---------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed</span>

从上面的执行计划可以看出count(*)和count(val)是一样快的。

2、建立索引做比较

<span style="font-size:14px;">SQL> create index idx_object_id on echo(object_id);索引已创建。已用时间: 00: 00: 05.69SQL> select count(*) from echo; COUNT(*)----------已用时间: 00: 00: 00.05执行计划----------------------------------------------------------Plan hash value: 99109176-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| ECHO | 80064 | 290 (1)| 00:00:04 |-------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processedSQL> select count(object_id) from echo;COUNT(OBJECT_ID)----------------已用时间: 00: 00: 00.08执行计划----------------------------------------------------------Plan hash value: 1131838604---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 49 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 80064 | 1016K| 49 (0)| 00:00:01 |---------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed</span>

哇，原来真的是用count(val)比count(*)要快啊，因为count(*)不能用到索引，而count(val)可以，真相真是如此吗？
3、将object_id设置为非空SQL> alter table echo modify object_id not null;

<span style="font-size:14px;">SQL> alter table echo modify object_id not null;表已更改。已用时间: 00: 00: 01.41SQL> select count(*) from echo; COUNT(*)----------已用时间: 00: 00: 00.00执行计划----------------------------------------------------------Plan hash value: 1131838604-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 49 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 80064 | 49 (0)| 00:00:01 |-------------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processedSQL> select count(object_id) from echo;COUNT(OBJECT_ID)----------------已用时间: 00: 00: 00.01执行计划----------------------------------------------------------Plan hash value: 1131838604-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 49 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 80064 | 49 (0)| 00:00:01 |-------------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed</span>

--看来count(val)和count(*)其实一样快，如果索引列是非空的，count(*)可用到索引，此时一样快！真相真是如此吗？
其实两者根本没有可比性，性能比较首先考虑写法等价，这两个语句根本就不等价。

结论：

其实优化器里的算法是这么玩的，列的偏移量决定性能，列越靠后，访问的开销越大。由于count(*)的算法与列偏移量无关，所以count(*)最快，count(最后列val)最慢。

3、用实验验证上面的结论

<span style="font-size:14px;">SQL> set serveroutput onSQL> set echo onSQL> drop table t purge;表已删除。----构造出有25个字段的表T----DECLARE l_sql VARCHAR2(32767);BEGIN l_sql := 'CREATE TABLE t ('; FOR i IN 1..25 LOOP l_sql := l_sql || 'n' || i || ' NUMBER,'; END LOOP; l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10'; EXECUTE IMMEDIATE l_sql;END; /PL/SQL 过程已成功完成。----将记录还有这个表T中填充----DECLARE l_sql VARCHAR2(32767);BEGIN l_sql := 'INSERT INTO t SELECT '; FOR i IN 1..25 LOOP l_sql := l_sql || '0,'; END LOOP; l_sql := l_sql || 'NULL FROM dual CONNECT BY level <= 10000'; EXECUTE IMMEDIATE l_sql; COMMIT;END; /PL/SQL 过程已成功完成。execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')SELECT num_rows, blocks FROM user_tables WHERE table_name = 'T';PL/SQL 过程已成功完成。SQL> NUM_ROWS BLOCKS---------- ---------- 80----以下动作观察执行速度，比较发现count(*)最快，count(最大列)最慢----SQL> DECLARE l_dummy PLS_INTEGER; l_start PLS_INTEGER; l_stop PLS_INTEGER; l_sql VARCHAR2(100);BEGIN l_start := dbms_utility.get_time; FOR j IN 1..1000 LOOP EXECUTE IMMEDIATE 'SELECT count(*) FROM t' INTO l_dummy; END LOOP; l_stop := dbms_utility.get_time; dbms_output.put_line((l_stop-l_start)/100); FOR i IN 1..25 LOOP l_sql := 'SELECT count(n' || i || ') FROM t'; l_start := dbms_utility.get_time; FOR j IN 1..1000 LOOP EXECUTE IMMEDIATE l_sql INTO l_dummy; END LOOP; l_stop := dbms_utility.get_time; dbms_output.put_line((l_stop-l_start)/100); END LOOP;END; /.18.33.39.38.42.4.45.49.48.46.48.48.55.51.56.57.61.62.75.67.68.7.73.78.77.81PL/SQL 过程已成功完成。</span>

三、验证count(*)和count(1)
沿用TOM大师的解释：