count(*)和sum(*)的結果有時候是一樣的，所以有時候開放在寫存儲過程的時候會用到sum當count使用

這樣有不有問題呢？我們來討論下這2個的效率。

?

    SQL> oradebug event 10046 trace name context forever,level 12;

Statement processed.

SQL>  select count(*) from business.PRPLCERTIFYIMGTEMPBAK;



  COUNT(*)

----------

 103287157



Elapsed: 00:00:10.01

SQL> select sum(1) from business.PRPLCERTIFYIMGTEMPBAK;



    SUM(1)

----------

 103287157



Elapsed: 00:00:13.27

SQL> oradebug event 10046 trace name context off;

Statement processed.

SQL> oradebug tracefile_name
  

從10046的信息，我們很容易就發現。sum的效率不足體現在cpu上，I/O完全一樣。

?

?

    select count(*) 

from

 business.PRPLCERTIFYIMGTEMPBAK





call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.02          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      9.98       9.75     203828     203874          0           1

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

total        4      9.98       9.77     203828     203874          0           1



Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS



Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=203874 pr=203828 pw=0 time=9750091 us)

103287157   INDEX FAST FULL SCAN IDX_SERIALNO (cr=203874 pr=203828 pw=0 time=309861672 us)(object id 155232)





Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file scattered read                      12791        0.00          0.65

  SQL*Net message from client                     2        3.47          3.47

********************************************************************************



select sum(1) 

from

 business.PRPLCERTIFYIMGTEMPBAK





call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2     13.26      12.95     203828     203874          0           1

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

total        4     13.26      12.95     203828     203874          0           1



Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS



Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=203874 pr=203828 pw=0 time=12957830 us)

103287157   INDEX FAST FULL SCAN IDX_SERIALNO (cr=203874 pr=203828 pw=0 time=206574428 us)(object id 155232)





Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file scattered read                      12791        0.00          0.63

  SQL*Net message from client                     2       10.56         10.56
  

為什么sum的效率不如count呢。那就要了解count和sum的算法了
舉例說明他的算法：


在排序統計的時候
sum的算法為1+1+1+1+1+1=6，sum是未知的，需要額外的CPU每一步都需要計算
count是1+2+3=6，就是說count的計算公式是定的，發現一行，加1，那么就是1，下一次是從2開始加
顯然sum的效率不如count。

?

所以開發童鞋們。在遇到類似的時候盡量使用count，而不用sum。

?

count(*)和sum(1) 的效率