count(1) is error-free in the SQL syntax , is that right ?count(1) is same as count('A') or count(whatever constant value) it will return the number of records from the SQL fetch .the result of count(*) and count(1) should be same but the performance is different . That's why I raise this question ,
测试环境:win2000 server,oracle 8.1.5 SQL> select count(*) from a; COUNT(*) --------- 262144实际:1352SQL> select count(1) from a; COUNT(1) --------- 262144实际:1472 测试结果表明使用count(*)要略快于count(1)
select count('l') from table是可以的 但select count(l) from table是不可以的 是select count(1) from table吧?SQL> select count(l) from aa; select count(l) from aa * ERROR at line 1: ORA-00904: invalid column name
To bzszp(SongZip) : if you have not generate the statistics for your schema , I think you are using RBO . To change from RBO to CBO, you can delete the statistics by using DBMS_UTILITY
ATCG(ATCG) : count(1) , it's Number 1 not character 'L'
是啊!在SQLPLUS里面 1 和 l 是不一样的,怎么在CSDN里 就一样了 我怎么看都像是 L的小写。
一般来说count(1)和count(rowid)比count(*)要快.
当然是count(1),但最快的是count(rowid)
我以前试过count(col1) from table;和count(*) from table,结果是count(col1)快。col1是table的某一列。 我觉得应该是count(1)快,但没有测试过。
it will return the number of records from the SQL fetch .the result of count(*) and count(1) should be same but the performance is different . That's why I raise this question ,
SQL> select count(*) from a; COUNT(*)
---------
262144实际:1352SQL> select count(1) from a; COUNT(1)
---------
262144实际:1472
测试结果表明使用count(*)要略快于count(1)
---------
1664990 real: 4706
SQL> select count(1) from t01; COUNT(1)
---------
1664990 real: 8081
表a中没有主键,也没有使用索引,这样对使用那种优化器有关系么?
如何改成rbo方式呢?
但select count(l) from table是不可以的
是select count(1) from table吧?SQL> select count(l) from aa;
select count(l) from aa
*
ERROR at line 1:
ORA-00904: invalid column name
count(1) , it's Number 1 not character 'L'
我怎么看都像是 L的小写。
我觉得应该是count(1)快,但没有测试过。