count(*)与count(1)有区别吗？

最新推荐文章于 2024-05-08 10:58:27 发布

原创最新推荐文章于 2024-05-08 10:58:27 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #oracle #access #system #table #disk

Oracle 专栏收录该内容

12 篇文章

订阅专栏

本文通过实测对比了SQL中使用count(*)和count(1)时的资源消耗情况，发现两者在资源消耗上几乎一致，揭示了SQL查询性能的常见误区。

一直以为sql中count(*)会比count(1)更消耗资源，今天实际分析比较下发现其实是一样的！

先看count(*):

[oracle@single bdump]$ sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Mar 21 10:21:21 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

idle>startup
ORACLE instance started.

Total System Global Area 120017880 bytes
Fixed Size 451544 bytes
Variable Size 67108864 bytes
Database Buffers 50331648 bytes
Redo Buffers 2125824 bytes
Database mounted.
Database opened.
idle>set autot on
idle>select count(*) from scott.test;

COUNT(*)
----------
282912

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
164 recursive calls
0 db block gets
3462 consistent gets
3439 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

再看count(1):

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Mar 21 10:22:29 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

idle>startup
ORACLE instance started.

Total System Global Area 120017880 bytes
Fixed Size 451544 bytes
Variable Size 67108864 bytes
Database Buffers 50331648 bytes
Redo Buffers 2125824 bytes
Database mounted.
Database opened.
idle>set autot on
idle>select count(1) from scott.test;

COUNT(1)
----------
282912

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
164 recursive calls
0 db block gets
3462 consistent gets
3439 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

二者的资源消耗是一致的，没看出有多大区别。