count(*)内幕？

[MountLion]

一个大约3M条记录的表，运行查询select count(*) from tablename,需要大约15秒钟。
机器：alpha8200小型机，Oracle8.1.6。
我的问题：这样一个查询究竟是怎样执行的呢，为什么比想象的要慢很多？

[nononono]

有主键吗? 试试下面的是不是快些:
select count(KeyField) from tablename

[wxcwuxuchun]

求记录数应该很快？

[xmao]

count（加索引的字段）速度会很快！

[zhuzhichao]

count(1)也不慢.

[pzou]

我知道在
mysql 中 count(*) 是属于 优化的语句。速度非常的快，但在其他数据库中就不太清楚了。好像在ACCESS 中根本就用 count(*) 得不出结果来

[KingSunSha]

我试了一下count(1)、count(索引的字段)、count(*)的速度是一样的。看以来ORACLE在执行count(*)的时候会自动按照最佳索引查找。正在找关于oracle tuning的资料，找到以后再和大家交流。
To: MountLion，看来你还要看看服务器的优化，恐怕从SQL方面很难提高

[oldwain]

asktom上说count(*), count(1), count(uniquecolumn)应该完全一样（没有任何区别）。
不过，我做了大量实验，认为某些情况下还是有区别的，感兴趣的话，我可以贴出来。

另外，如果表上有unique index, count(*)会比较快一些，这种情况下，count(*)会只对该index进行range scan. 当然，前提是采用cbo, 且对该表执行过analyze.

不过，无论如何，对1m纪录的表求count,都不会太快，毕竟要进行一次full scan(要么是对表本身，要么对unique index).

[KingSunSha]

To: oldwain(老斗) 能贴出来吗？谢谢！

[wx_zhang]

用count(*)查找是全表扫描，很慢。

[MountLion]

噢

[michaelzhang]

ORACLE的count(*)要进行full scan，所以肯定很慢。

[Jazzlover]

哪有那么慢，我现在记不清了，不过好象用sql server,奔3 （？百），大于50万条记录也不要15秒。

[happyer]

MountLion
" 一个大约3M条记录的表，"运行查询select count(*) from tablename,需要大约15秒钟。
你是说有3百万条记录吗？ M好像是兆的意思，也就是10^6，很恐怖噢！
看来速度真是很快。
我建议你另建一个表（记录这个表的记录数量），每次insert/delete时，就改变新建表的值，OK? 我估计你不会经常进行写操作。再说每次多写这么一个字段，不算浪费资源吧。
以后你统计起来也很方便。干吧。

[oldwain]

以下为我的实验的脚本，有兴趣的朋友可以用此脚本测试，最好能贴回测试结论。
asktom上tom用同样的脚本的到的结果，却证实count(*)与count(1)相同，很奇怪！

在svrmgrl下运行(set timing on 在svrmgrl比sqlplus提供的信息较详细）

drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;
commit;
set timing on
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;

-- 每个语句运行两次，确保排除cache的影响

以下为我的测试结果（环境 817/win2000)：

===========================

C:\>svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SVRMGR> connect scott/tiger
Connected.
SVRMGR> insert into r1000 select seq_r1000.nextval from all_objects where
rownum<1001;
1000 rows processed.
SVRMGR> commit;
Statement processed.
SVRMGR> set timing on
Timing ON
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.33 (Elapsed) 0.00 (CPU)
Total 1.33 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.02 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.36 (Elapsed) 0.00 (CPU)
Total 2.38 0.00
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.34 (Elapsed) 0.00 (CPU)
Total 1.35 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.33 (Elapsed) 0.00 (CPU)
Total 2.33 0.00
SVRMGR>

============================


以下为tom的测试结果：

============================

drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;

analyze table r1000 compute statistics;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;


alter session set sql_trace=true;

declare
n number;
begin
for i in 1 .. 10
loop
select count(*) into n from r1000, r1000;
select count(1) into n from r1000, r1000;
end loop;
end;
/


shows:

SELECT COUNT(*)
FROM
R1000,R1000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 12.46 12.53 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.46 12.53 0 40 80 10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000

********************************************************************************

SELECT COUNT(1)
FROM
R1000,R1000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 10 0.00 0.01 0 0 0 0
Fetch 10 12.38 12.38 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.38 12.40 0 40 80 10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000



注意：我使用了set timing on, 而tom使用的是sqltrace, 理论上讲sqltrace更为精确，不过我通过sqltrace看过同样有30%-50%差异（该结果未保存，故无法提供）。


希望各位测试后，能把结论贴出来。

[zming]

不愧是小型机，我的Sun Ultra5d，Informix On-line 9.13 UC2，50万条记录，
Select count(*) from tablename;
60秒

[MountLion]

大约2,800,000条记录，每行的宽度有553字节。
我不是DBA，只是用户，做个实验而已。
该表基本上不需要delete，只是要经常insert,update和select。
大概有些数据库会记录表的当前记录数（可能还要针对用户吧），Oracle不会这样做

[oldwain]

以下为我的实验的脚本，有兴趣的朋友可以用此脚本测试，最好能贴回测试结论。
asktom上tom用同样的脚本的到的结果，却证实count(*)与count(1)相同，很奇怪！

在svrmgrl下运行(set timing on 在svrmgrl比sqlplus提供的信息较详细）

drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;
commit;
set timing on
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;

-- 每个语句运行两次，确保排除cache的影响

以下为我的测试结果（环境 817/win2000)：

===========================

C:\>svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SVRMGR> connect scott/tiger
Connected.
SVRMGR> insert into r1000 select seq_r1000.nextval from all_objects where
rownum<1001;
1000 rows processed.
SVRMGR> commit;
Statement processed.
SVRMGR> set timing on
Timing ON
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.33 (Elapsed) 0.00 (CPU)
Total 1.33 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.02 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.36 (Elapsed) 0.00 (CPU)
Total 2.38 0.00
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.34 (Elapsed) 0.00 (CPU)
Total 1.35 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.33 (Elapsed) 0.00 (CPU)
Total 2.33 0.00
SVRMGR>

============================


以下为tom的测试结果：

============================

drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;

analyze table r1000 compute statistics;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;


alter session set sql_trace=true;

declare
n number;
begin
for i in 1 .. 10
loop
select count(*) into n from r1000, r1000;
select count(1) into n from r1000, r1000;
end loop;
end;
/


shows:

SELECT COUNT(*)
FROM
R1000,R1000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 12.46 12.53 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.46 12.53 0 40 80 10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000

********************************************************************************

SELECT COUNT(1)
FROM
R1000,R1000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 10 0.00 0.01 0 0 0 0
Fetch 10 12.38 12.38 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.38 12.40 0 40 80 10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000



注意：我使用了set timing on, 而tom使用的是sqltrace, 理论上讲sqltrace更为精确，不过我通过sqltrace看过同样有30%-50%差异（该结果未保存，故无法提供）。


希望各位测试后，能把结论贴出来。