count(*)与count(1)区别何在?
count(*)与count(1)区别何在?
count(*)与count(1)区别何在?
解决方案 »
- Oracle where rownum语句与Order by的优先级
- 如何建空的BLOB参数?------------------急!!!!!!!!
- 关于oracle 10g 无法删除用户,session中不存在用户的会话
- 数据库编码,下载的文件名为乱码
- sql查询问题。
- 【寻助】在PLsql中如何将一中文内容的列让其右对齐?(plsql自带的右对齐只针对数据,不针对中文)
- PLSQL developer6.0.5里编辑BLOB字段时出现"无效的窗口句柄"
- str_CurMonth M_每日.当月%TYPE;是什么意思?
- 有誰裝過Oracle 10g?
- 如何将数据从SQL Server 2000中导入到oracle
- sqlplus的快捷键,怎么也想不起来了。
- ORACLE客户端导入/导出数据的问题!
*是一个通配符,它代表的是数据行的所有字段,而一的话就意味着你有一行的话就以一个1来代替
所以count(*) 比 count(1) 的速度应该要慢些。
经常有人说count(1)比count(*)快。实际上是这样吗?不是的,作为数据库的衡量性能的逻辑读来说,是没有差异的。但是在实际的多次反复测试中,我们可以来对比看看真实状况。SQL> create table t as select * from all_objects;Table created.SQL> select count(*) from all_objects; COUNT(*)
----------
3789SQL> 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) :79PL/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) :82PL/SQL procedure successfully completed.
从这里我们可以看出在时间的对比上,同样通过全表扫描和走索引,除了count(rowid)在走索引的时候明显慢外,其他情况下差异都不是很大,很明显,对于这样的求和来说,我们使用 count(*) 是没有什么不好的,count(1)更快纯粹是一个谬论。更多的讨论可以参考http://www.cnoug.org/viewthread.php?tid=857&highlight=count%2Bbiti_rainy
应该一样,都是取一个相同结果,
如果是count(distinct Field_name)
那就不一样了