Oracle count哪种写法更快
1)创建测试表test@CISCOSYS> create table t as select * from dba_objects;
表已创建。
test@CISCOSYS> update t set object_id =rownum ;
已更新50967行。
2)使用count(*)进行统计
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用时间:00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
|>
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 |SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
4recursive calls
0db block gets
764consistent gets
0physical reads
0redo>
410bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
3)使用COUNT(列）进行统计
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用时间:00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
|>
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 |SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
4recursive calls
0db block gets
764consistent gets
0physical reads
0redo>
410bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
解释一下 :物理读为0，是因为创建表的时候，数据已经载入load buffer.
可以使用
test@CISCOSYS>>
通过比较COUNT(*) 和Count(列） ,两种情况的COST 是完全一样的。
继续试验！！！
为表创建索引
test@CISCOSYS> create index>
索引已创建。
test@CISCOSYS>>
系统已更改。
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用时间:00: 00: 00.26
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
|>
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 |SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
5recursive calls
0db block gets
765consistent gets
705physical reads
0redo>
410bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
test@CISCOSYS> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
50967
已用时间:00: 00: 00.09
执行计划
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------------
--
|>
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01
|
| 1 |SORT AGGREGATE | | 1 | 13 | |
|
| 2 | INDEX FAST FULL SCAN|>
|
--------------------------------------------------------------------------------
--
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
4recursive calls
0db block gets
181consistent gets
477physical reads
0redo>
418bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
在这里，用COUNT(列）比COUNT(*)要快。通过比较执行计划。可以看出COUNT(*)不能用到索引，而COUNT(列)可以
继续试验！！！
将键值设为非空
test@CISCOSYS>>
表已更改。
已用时间:00: 00: 01.34
test@CISCOSYS>>
系统已更改。
已用时间:00: 00: 00.01
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用时间:00: 00: 00.31
执行计划
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------
|>
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 |SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN|>
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
205recursive calls
0db block gets
213consistent gets
496physical reads
0redo>
410bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
5sorts (memory)
0sorts (disk)
1rows processed
test@CISCOSYS>>
系统已更改。
已用时间:00: 00: 00.04
test@CISCOSYS> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
50967
已用时间:00: 00: 00.20
执行计划
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------
|>
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 |SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN|>
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
4recursive calls
0db block gets
181consistent gets
477physical reads
0redo>
418bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
将一些记录object_id置为null.
test@CISCOSYS>>
表已更改。
test@CISCOSYS> update t set object_id=null where object_id select count(*) from t;
COUNT(*)
----------
50967
已用时间:00: 00: 00.00
test@CISCOSYS> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
50957
发现count(*)和count(列）记录不一样。也就是说，两个功能上根本不是等价的。
如果一个列上存在索引，且非空。 COUNT(*)和COUNT(列)功能相当。
反之，COUNT(*) 和COUNT(列）两者功能本身就功能不同，不应等同对待。
页:
[1]