select count(*) from table 性能

[cjy218]

select count(*) from table 性能..有比这语句一样的....

[Jane_64]

什么意思?不明白

[robin_ares]

说中文吧

[xudaqing2008]

select count(0) from table

[xudaqing2008]

[Quote=引用 4 楼 cjy218 的回复:]
一个700百万记录的表
现在要统计表的记录数 怎么用最优方法获得记录总数
[/Quote]
select count(0) from table是最优的，你可以试一试

[cjy218]

一个700百万记录的表
现在要统计表的记录数 怎么用最优方法获得记录总数

[ruihuahan]

force 一个短行的索引吧，可以减少 IO。

[clarkchiang]

select count(0) from table 比 select count(*) from table 速度快

[tchx]

我测试过1千多万的，COUNT(*)比COUNT(1)之类速度要快一点。

[hebo2005]

[Quote=引用 7 楼 lightning8 的回复:]
select count(0) from table 比 select count(*) from table 速度快
[/Quote]

只能是据说
还有种说法，这两种的速度是一样的

另外还有种写法
count(索引字段)
据说是最快的

[wll123a123]

呵呵，这个问题有趣

[lilingbill]

主要看你的表建立主键或者索引与否。如果建立 了的，那么count(*)和count(1)差不多的执行效率。优化器都将从索引中去获取全部总记录数。而不是从硬盘上。

[8193102]

select count(*) 似乎快一些,因为实际上它用的是rowid, 大部分情况是这样

[8193102]

不相信的兄弟,完全可以找多个试试

[colinTongzw]

count(*)不比count(字段)慢

　　某些资料上说：用*会统计所有列，显然要比一个世界的列名效率低。这种说法其实是没有根据的。我们来看：

select count(*) from Tgongwen

　　用时：1500毫秒

select count(gid) from Tgongwen

　　用时：1483毫秒

select count(fariqi) from Tgongwen

　　用时：3140毫秒

select count(title) from Tgongwen

　　用时：52050毫秒

　　从以上可以看出，如果用count(*)和用count(主键)的速度是相当的，而count(*)却比其他任何除主键以外的字段汇总速度要快，而且字段越长，汇总的速度就越慢。我想，如果用count(*)， SQL SERVER可能会自动查找最小字段来汇总的。当然，如果您直接写count(主键)将会来的更直接些。

[wffffc]

SQL> select count(*) from emp;

COUNT(*)
----------
14


执行计划
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
301 recursive calls
0 db block gets
56 consistent gets 第一次执行没有将数据进行缓存,看不出问题
8 physical reads 0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from emp;

COUNT(*)
----------
14


执行计划
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads 0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(empno) from emp;

COUNT(EMPNO)
------------
14


执行计划
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads 0 redo size
423 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(ename) from emp;

COUNT(ENAME)
------------
14


执行计划
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------


统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
10 consistent gets
8 physical reads 0 redo size
423 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(ename) from emp;

COUNT(ENAME)
------------
14


执行计划
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets 0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这样看来count(*) 和count(索引列)差不多,count(非索引列)效果比较差了

[8193102]

所以总要自己试试,不同版本的ORACLE都会有不同的惊喜.

[zxg1127_1]

学习 了