Ask Hoegh(4)——select count(*)和select count(1)、count(column)有區別嗎?

Hoegh發表於2016-02-03
問:select count(*)可以用來查詢表的記錄數,聽說count(1)、count(主鍵)、count(非主鍵列)也可以,這幾個有什麼區別嗎?

答:不考慮Null的情況
  • count(1)和count(主鍵)、count(*) 這幾個只掃描主鍵Index就可以得到資料
  • count(非主鍵列)是掃描表的
  • 所以相對來說,count(1)和count(主鍵)這兩個效率高
  • 還有一種寫法是count(ROWID)這也是隻掃描Index的,效率高
  • 事實上,效率上的問題不用考慮太多,count(*),count(1)和count(主鍵)都沒錯,結合實際資料庫執行一下,選擇你合適的就可以
考慮Null的情況
  • count(主鍵)肯定沒有空值
  • count(*)和count(1)能取出含有空值的所有記錄數
  • count(非主鍵列)不含空值
以hr測試使用者下的employees表為例,分別輸出查詢結果以及執行計劃,便於大家進行對比分析。

查詢結果

SQL> select count(*) from employees;    

 COUNT(*)
----------
      107

SQL> select count(1) from employees;

 COUNT(1)
----------
      107

SQL> select count(employee_id) from employees;--主鍵列

COUNT(EMPLOYEE_ID)
------------------
              107

SQL> select count(commission_pct) from employees;--非主鍵列(包含空值)

COUNT(COMMISSION_PCT)
---------------------
                  35

SQL> select count(rowid) from employees;

COUNT(ROWID)
------------
        107

執行計劃

SQL> set autot trace exp
SQL> select count(*) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945

-------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

SQL> select count(1) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945

-------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

SQL> select count(employee_id) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945

-------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

SQL> select count(commission_pct) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     2 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |     2 |            |          |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   214 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL> select count(rowid) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |    12 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |    12 |            |          |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |  1284 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------


~~~~~~~ the end~~~~~~~~~
hoegh
2016.02.03


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30162081/viewspace-1986201/,如需轉載,請註明出處,否則將追究法律責任。

相關文章