count(*)、count(1)、count(rowid)的对比

原文作者 bitirainy

http://bitirainy.itpub.net/post/330/1914

经常有人说count(1)比count(*)快。实际上是这样吗？不是的，作为数据库的衡量性能的逻辑读来说，是没有差异的。但是在实际的多次反复测试中，我们可以来对比看看真实状况。

SQL> create table t as select * from all_objects;

Table created.

SQL> select count(*) from all_objects;

COUNT(*)
----------
3789

SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

先 delete statistics , 各方式分别求 1000次 count
然后 analyze table ,各方式分别求 1000次 count
然后做个时间对比

服务器无外界干扰的情况下的反复测试的结果如下
declare
n_count number;
n_time0 number;
n_time1 number;
n_time2 number;
n_time3 number;
n_time4 number;

begin
execute immediate'analyze table t delete statistics';


select count(*) into n_count from t;

n_time0 := dbms_utility.get_time;
for i in 1..1000 loop
select /*+index(t t_index)*/ count(*) into n_count from t;
end loop;

n_time1 := dbms_utility.get_time;
for i in 1..1000 loop
select count(*) into n_count from t;
end loop;

n_time2 := dbms_utility.get_time;

for i in 1..1000 loop
select count(1) into n_count from t;
end loop;
n_time3 := dbms_utility.get_time;
for i in 1..1000 loop
select count(rowid) into n_count from t;
end loop;
n_time4 := dbms_utility.get_time;

dbms_output.put_line('the count of the table T : '||to_char(n_count));
dbms_output.put_line('before analyze ,loop 1000,/*+hints*/count(*) :'||to_char(n_time1 - n_time0 ));
dbms_output.put_line('before analyze ,loop 1000,count(*) :'||to_char(n_time2 - n_time1));
dbms_output.put_line('before analyze ,loop 1000,count(1) :'||to_char(n_time3 - n_time2));
dbms_output.put_line('before analyze ,loop 1000,count(rowid) :'||to_char(n_time4 - n_time3));

execute immediate'analyze table t compute statistics';

select count(*) into n_count from t;
n_time1 := dbms_utility.get_time;
for i in 1..1000 loop
select count(*) into n_count from t;
end loop;

n_time2 := dbms_utility.get_time;

for i in 1..1000 loop
select count(1) into n_count from t;
end loop;
n_time3 := dbms_utility.get_time;
for i in 1..1000 loop
select count(rowid) into n_count from t;
end loop;
n_time4 := dbms_utility.get_time;

dbms_output.put_line('the count of the table T : '||to_char(n_count));
dbms_output.put_line('after analyze ,loop 1000,count(*) :'||to_char(n_time2 - n_time1));
dbms_output.put_line('after analyze ,loop 1000,count(1) :'||to_char(n_time3 - n_time2));
dbms_output.put_line('after analyze ,loop 1000,count(rowid) :'||to_char(n_time4 - n_time3));

end;

the count of the table T : 3789
before analyze ,loop 1000,/*+hints*/count(*) :71
before analyze ,loop 1000,count(*) :63
before analyze ,loop 1000,count(1) :91
before analyze ,loop 1000,count(rowid) :88
the count of the table T : 3789
after analyze ,loop 1000,count(*) :28
after analyze ,loop 1000,count(1) :28
after analyze ,loop 1000,count(rowid) :79

PL/SQL procedure successfully completed.

SQL> /
the count of the table T : 3789
before analyze ,loop 1000,/*+hints*/count(*) :72
before analyze ,loop 1000,count(*) :61
before analyze ,loop 1000,count(1) :88
before analyze ,loop 1000,count(rowid) :85
the count of the table T : 3789
after analyze ,loop 1000,count(*) :28
after analyze ,loop 1000,count(1) :27
after analyze ,loop 1000,count(rowid) :82

PL/SQL procedure successfully completed.

从这里我们可以看出在时间的对比上，同样通过全表扫描和走索引，除了count(rowid)在走索引的时候明显慢外，其他情况下差异都不是很大，很明显，对于这样的求和来说，我们使用 count(*) 是没有什么不好的，count(1)更快纯粹是一个谬论。

更多的讨论可以参考

http://www.cnoug.org/viewthread.php?tid=857&highlight=count%2Bbiti_rainy