数据库效率问题 select count(*) from tbname 比select count(1) from tbname 快 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select count(*) 应该是最慢的吧? select count(非空的字段) from tbname; select count(*) from tbname 比select count(1) from tbname 快--从测试计划上来看没有索引时.SQL> select count(*) from testa;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'TESTA'Statistics---------------------------------------------------------- 0 recursive calls 4 db block gets 1 consistent gets 2 physical reads 0 redo size 152 bytes sent via SQL*Net to client 308 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select count(id) from testa;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'TESTA'Statistics---------------------------------------------------------- 0 recursive calls 4 db block gets 1 consistent gets 2 physical reads 0 redo size 170 bytes sent via SQL*Net to client 308 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> http://blog.itpub.net/post/330/1914 --有索引时,不管count()里面是用*还是字段,都将使用第一个索引.SQL> select count(*) from fnd_user;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'FND_USER_U1' (UNIQUE) (Cost=1 Card =510)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 170 bytes sent via SQL*Net to client 308 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select count(user_name) from fnd_user;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'FND_USER_U1' (UNIQUE) (Cost=1 Card =510)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 178 bytes sent via SQL*Net to client 308 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select count(user_id) from fnd_user;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'FND_USER_U1' (UNIQUE) (Cost=1 Card =510)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 176 bytes sent via SQL*Net to client 308 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select count(*) from tbname 和select count(1) from tbname从执行计划上看,没有什么不同 oracle表的修改 oracle8.1.7.4在windows2003不能安装 紧急求助 SQL语句编译后提示缺失右括号 !! 在库之间导表问题,急急 !! oracle 10g 备份和恢复问题 ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME 调试pl/sql代码问题 Oracle 8.1.6: ORA-12571: TNS:packet writer failure EXP导出时出错,请帮忙看看,感谢! 序列的格式问题 存储过程无效的问题 如何将用户1,exp出来的备份文件用imp导入到用户2中
比
select count(1) from tbname 快
--从测试计划上来看
没有索引时.
SQL> select count(*) from testa;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TESTA'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
2 physical reads
0 redo size
152 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> select count(id) from testa;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TESTA'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
2 physical reads
0 redo size
170 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL>
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'FND_USER_U1' (UNIQUE) (Cost=1 Card
=510)Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
170 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> select count(user_name) from fnd_user;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'FND_USER_U1' (UNIQUE) (Cost=1 Card
=510)Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
178 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processedSQL> select count(user_id) from fnd_user;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'FND_USER_U1' (UNIQUE) (Cost=1 Card
=510)Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
176 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processedSQL>
和
select count(1) from tbname从执行计划上看,没有什么不同