今天和网友一起讨论count(1) count（*） count(primary key)三个在性能上的优越性，测试结果区别不是很大,oracle 10G windows的生产库下，数据交换应该属于较小的。

SQL> select count(*) from ycjhup.inf_apply_process;

COUNT(*)

----------

191651

已用时间: 00: 00: 00.42

执行计划

----------------------------------------------------------

Plan hash value: 197964574

--------------------------------------------------------------------------------

-----------

| Id | Operation | Name | Rows | Cost (%CPU)|

Time |

--------------------------------------------------------------------------------

-----------

| 0 | SELECT STATEMENT | | 1 | 117 (2)|

00:00:02 |

| 1 | SORT AGGREGATE | | 1 | |

|

| 2 | INDEX FAST FULL SCAN| INF_APPLY_PRO_CREATE_DATE | 179K| 117 (2)|

00:00:02 |

--------------------------------------------------------------------------------

-----------

统计信息

----------------------------------------------------------

4482 recursive calls

0 db block gets

1591 consistent gets

664 physical reads

0 redo size

338 bytes sent via SQL*Net to client

337 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

130 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.03

SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.03

————————————————————————————————————

SQL> select count(1) from ycjhup.inf_apply_process;

COUNT(1)

----------

191651

已用时间: 00: 00: 00.35

执行计划

----------------------------------------------------------

Plan hash value: 197964574

--------------------------------------------------------------------------------

-----------

| Id | Operation | Name | Rows | Cost (%CPU)|

Time |

--------------------------------------------------------------------------------

-----------

| 0 | SELECT STATEMENT | | 1 | 117 (2)|

00:00:02 |

| 1 | SORT AGGREGATE | | 1 | |

|

| 2 | INDEX FAST FULL SCAN| INF_APPLY_PRO_CREATE_DATE | 179K| 117 (2)|

00:00:02 |

--------------------------------------------------------------------------------

-----------

统计信息

----------------------------------------------------------

4066 recursive calls

0 db block gets

1519 consistent gets

649 physical reads

0 redo size

338 bytes sent via SQL*Net to client

337 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

124 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.04

SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.03

-______________________________________________________________________________

SQL> select count(no) from ycjhup.inf_apply_process;

COUNT(NO)

----------

191651

已用时间: 00: 00: 00.42

执行计划

----------------------------------------------------------

Plan hash value: 197964574

--------------------------------------------------------------------------------

-----------

| Id | Operation | Name | Rows | Cost (%CPU)|

Time |

--------------------------------------------------------------------------------

-----------

| 0 | SELECT STATEMENT | | 1 | 117 (2)|

00:00:02 |

| 1 | SORT AGGREGATE | | 1 | |

|

| 2 | INDEX FAST FULL SCAN| INF_APPLY_PRO_CREATE_DATE | 179K| 117 (2)|

00:00:02 |

--------------------------------------------------------------------------------

-----------

统计信息

----------------------------------------------------------

4454 recursive calls

0 db block gets

1587 consistent gets

663 physical reads

0 redo size

339 bytes sent via SQL*Net to client

337 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

130 sorts (memory)

0 sorts (disk)

1 rows processed

其中no是主键，inf_apply_process表的数据大概有20W的样子，可以看出测试的结果区别不是很大，而且tom在8I后提到区别不大，不过个人还是觉得存在count(1)>count(no)>count(*)的。

[@more@]