看到别人使用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
二者的资源消耗是一致的,没看出有多大区别。在tom的网站上,有这样一段:
You Asked
and we said...
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-573277/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-573277/