如果null参与聚集运算,则除count(*)之外其它聚集函数都忽略null.如: ID DD 1 e 2 null select count(*) from table --结果是2 select count(DD) from table ---结果是1有说count(1)效率高,感觉差不多..没啥区别
经常有人说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 然后做个时间对比
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;
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)更快纯粹是一个谬论。
ID DD
1 e
2 null
select count(*) from table --结果是2
select count(DD) from table ---结果是1有说count(1)效率高,感觉差不多..没啥区别
不考虑Null的情况
count(1)和count(主键) 这两个只扫描主键Index就可以得到数据,
count(*)是扫描表的。
所以count(1)和count(主键)这两个效率高。
还有一种写法是count(ROWID)这也是只扫描Index的,效率高。
我在300万业务数据上测试的结果是count(ROWID),count(1),count(主键)这个三种情况速度差不多,
count(*)这种明显慢,察看执行计划,COUNT(*)时候走的是全表查询。
----------
5288265已用时间: 00: 00: 07.51
SQL> select count(1) from ysgl_compile_reqsub; COUNT(1)
----------
5288265已用时间: 00: 00: 00.68
SQL> select count(id) from ysgl_compile_reqsub; COUNT(ID)
----------
5288265已用时间: 00: 00: 00.68
SQL> select count(rowid) from ysgl_compile_reqsub;COUNT(ROWID)
------------
5288265已用时间: 00: 00: 01.01
SQL> select count(rowid) from ysgl_vdata_his;COUNT(ROWID)
------------
5299458已用时间: 00: 00: 09.98
SQL> select count(*) from ysgl_vdata_his; COUNT(*)
----------
5299458已用时间: 00: 00: 00.93
SQL> select count(1) from ysgl_vdata_his; COUNT(1)
----------
5299458已用时间: 00: 00: 00.71
SQL> select count(1) from ysgl_excelbasic; COUNT(1)
----------
3755052已用时间: 00: 00: 04.60
SQL> select count(*) from ysgl_excelbasic; COUNT(*)
----------
3755052已用时间: 00: 00: 00.50
SQL>
实际应用中,你得看实际情况,没准这个表没有唯一键索引呢?在CBO的情况下,统计情报不准确呢?没准这种问题会出现好多版本的回答。测试环境不同结果也不一样,你随便键个表,看看它们的执行计划,就能看出他们的区别了。CSDN不能连续回复3次,害我等了好长时间楼上回复
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)更快纯粹是一个谬论。
但是在表做过分析之后,count(1)会比count(*)的用时少些(1w以内数据量),不过差不了多少。
这个也与表的记录数多少有关!如果1w以外的数据量,做过表分析之后,反而count(1)的用时比count(*)多了。另外,当数据量达到10w多的时候,使用count(1)要比使用count(*)的用时稍微少点!如果你的数据表没有主键,那么count(1)比count(*)快
如果有主键的话,那主键(联合主键)作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(1),用count(*),sql会帮你完成优化的
因此:count(1)和count(*)基本没有差别!sql调优,主要是考虑降低:consistent gets和physical reads的数量。
这个问题其实很无聊,莫要钻牛角尖,从count(*),count(id)等去调优,有些得不偿失。 UP.
SQL> select /*+ FIRST_ROWS */ count(*) from emp,emp,emp,emp,emp,emp,emp;
已用时间: 00: 00: 00.04执行计划
----------------------------------------------------------
Plan hash value: 2048615215-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1786K (1)| 05:57:13 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MERGE JOIN CARTESIAN | | 105M| 1786K (1)| 05:57:13 |
| 3 | MERGE JOIN CARTESIAN | | 7529K| 127K (1)| 00:25:32 |
| 4 | MERGE JOIN CARTESIAN | | 537K| 9121 (1)| 00:01:50 |
| 5 | MERGE JOIN CARTESIAN | | 38416 | 657 (1)| 00:00:08 |
| 6 | MERGE JOIN CARTESIAN | | 2744 | 51 (0)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 196 | 6 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | 1 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 14 | 5 (0)| 00:00:01 |
| 10 | INDEX FAST FULL SCAN| PK_EMP | 14 | 0 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 14 | 51 (0)| 00:00:01 |
| 12 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
| 13 | BUFFER SORT | | 14 | 657 (1)| 00:00:08 |
| 14 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
| 15 | BUFFER SORT | | 14 | 9120 (1)| 00:01:50 |
| 16 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
| 17 | BUFFER SORT | | 14 | 127K (1)| 00:25:32 |
| 18 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 14 | 1786K (1)| 05:57:13 |
| 20 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------SQL> select /*+ FIRST_ROWS */ count(1) from emp,emp,emp,emp,emp,emp,emp;
已用时间: 00: 00: 00.03执行计划
----------------------------------------------------------
Plan hash value: 2048615215-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1786K (1)| 05:57:13 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MERGE JOIN CARTESIAN | | 105M| 1786K (1)| 05:57:13 |
| 3 | MERGE JOIN CARTESIAN | | 7529K| 127K (1)| 00:25:32 |
| 4 | MERGE JOIN CARTESIAN | | 537K| 9121 (1)| 00:01:50 |
| 5 | MERGE JOIN CARTESIAN | | 38416 | 657 (1)| 00:00:08 |
| 6 | MERGE JOIN CARTESIAN | | 2744 | 51 (0)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 196 | 6 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | 1 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 14 | 5 (0)| 00:00:01 |
| 10 | INDEX FAST FULL SCAN| PK_EMP | 14 | 0 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 14 | 51 (0)| 00:00:01 |
| 12 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
| 13 | BUFFER SORT | | 14 | 657 (1)| 00:00:08 |
| 14 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
| 15 | BUFFER SORT | | 14 | 9120 (1)| 00:01:50 |
| 16 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
| 17 | BUFFER SORT | | 14 | 127K (1)| 00:25:32 |
| 18 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 14 | 1786K (1)| 05:57:13 |
| 20 | INDEX FAST FULL SCAN | PK_EMP | 14 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------