select count(*) from table 性能..有比这语句一样的....
解决方案 »
- 做开发的转做db 该看哪些关于oracle的书
- 求解一个SQL解析字符串的问题
- 请问在oracle的存储过程中设默认值呢
- 求一条SQL语句优化,一张300M的表查3分钟
- 怎样创建索引?
- 主表和流程表关联问题
- 大家看看这条统计语句怎么写?
- 数据库过滤部分数据
- ODBC连接ORACLE问题
- 很简单的存储过程(SQL Server2000转到Oracle8),比较急,在线等
- rac与单机的dataguard上switch over,出现问题
- 在.Net下连接oracle服务器时出现“服务器无法处理请求--->System.Data.OracleClient需要Client客户端软件8.1.7或更高版本”
只能是据说
还有种说法,这两种的速度是一样的另外还有种写法
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 processedSQL> 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 processedSQL> 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 processedSQL> 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 processedSQL> 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(非索引列)效果比较差了