SQL test 01 - Count Transformation
#
select count(x) from t
在開發時，我們經常會需要計算資料表中的資料筆數。這時候你可能會用到 COUNT(*)、COUNT(PK欄位) 或是 COUNT(一般欄位)。但你知道它們在效能上有什麼顯著的差異嗎？
這篇文章將透過實際測試，帶你了解是否有 Index (索引) 對於 COUNT 聚合函數查詢的巨大影響，以及為什麼我們應該謹慎選擇 COUNT 裡面的參數。
測試環境與 DDL
#
我們準備了一張名為 USERS_DETAILS_2023 的資料表，其中 MAL_ID 是 Primary Key (主鍵)，這意味著它會自動建立一個 Unique Index，且不允許有 NULL 值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
CREATE TABLE USERS_DETAILS_2023 (
MAL_ID NUMBER PRIMARY KEY,
USERNAME NVARCHAR2(255),
GENDER NVARCHAR2(50),
BIRTHDAY NVARCHAR2(100),
LOCATION NVARCHAR2(500),
JOINED NVARCHAR2(100),
DAYS_WATCHED NUMBER(10,2),
MEAN_SCORE NUMBER(5,2),
WATCHING NUMBER,
COMPLETED NUMBER,
ON_HOLD NUMBER,
DROPPED NUMBER,
PLAN_TO_WATCH NUMBER,
TOTAL_ENTRIES NUMBER,
REWATCHED NUMBER,
EPISODES_WATCHED NUMBER
);
|
測試結果與執行計畫 (Explain Plan)
#
我們將分別測試三種不同的 COUNT 寫法，並觀察 Oracle 優化器 (Optimizer) 給出的執行計畫與 Cost (成本)。
1. 測試：COUNT(PK 欄位)
#
MAL_ID 是這張表的主鍵 (PK)，自帶 Index。
1
|
SELECT COUNT(MAL_ID) FROM pollo.USERS_DETAILS_2023;
|
EXPLAIN PLAN
#
1
2
3
4
5
6
7
8
9
10
11
12
13
|
Plan hash value: 3668018772
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 409 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C0010904 | 932K| 409 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
|
分析：
因為 MAL_ID 有 Index，資料庫不需要去讀取龐大的原始資料表（Table），只需要去掃描體積小得多的 Index（INDEX FAST FULL SCAN），就能計算出總筆數。這裡的執行成本 (Cost) 僅為 409。
2. 測試：COUNT(*)
#
最常見的算總筆數寫法。
1
|
SELECT COUNT(*) FROM pollo.USERS_DETAILS_2023;
|
EXPLAIN PLAN
#
1
2
3
4
5
6
7
8
9
10
11
12
13
|
Plan hash value: 3668018772
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 409 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C0010904 | 932K| 409 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
|
分析：
結果與 COUNT(PK) 完全相同！當使用 COUNT(*) 時，Oracle 的優化器非常聰明，它知道你只是想算「有幾列（Rows）」，所以它會自動挑選該表中最輕量的一個 Index (通常就是 PK Index) 來進行掃描，因此成本同樣保持在很低的 409。
3. 測試：COUNT(一般欄位且未加 Index)
#
如果我們改用一個沒有建立 Index 的欄位，例如 USERNAME 呢？
1
|
SELECT COUNT(USERNAME) FROM pollo.USERS_DETAILS_2023;
|
EXPLAIN PLAN
#
1
2
3
4
5
6
7
8
9
10
11
12
13
|
Plan hash value: 4071893995
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 257 | 3814 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 257 | | |
| 2 | TABLE ACCESS FULL| USERS_DETAILS_2023 | 932K| 228M| 3814 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
|
分析：
這裡發生了 TABLE ACCESS FULL（全表掃描）！
為什麼？因為 COUNT(欄位) 的真實意義是「計算該欄位不為 NULL 的資料有幾筆」。由於 USERNAME 沒有 Index，資料庫別無選擇，只能把整張資料表從頭到尾翻一遍，一筆一筆檢查 USERNAME 是否為空值。
這導致查詢成本暴增將近 10 倍，Cost 來到了 3814！
總結與 Tuning 建議
#
透過上述實驗，我們可以得出幾個重要的結論：
- 效能差異巨大：有沒有使用到 Index，會讓
COUNT 的計算成本產生天壤之別（本例中成本是 409 vs 3814，相差近 10 倍）。在資料量高達幾百萬甚至上億筆時，這個差異會直接反映在查詢時間（幾秒鐘與幾十分鐘的區別）。
- 了解語意差異：
COUNT(*)：計算資料表的「總列數」(無論是否為 NULL)。COUNT(欄位)：計算該欄位中「不為 NULL」的筆數。
- 最佳實踐：如果你單純只是想知道「這張表有幾筆資料」，請永遠優先使用
COUNT(*) 或 COUNT(PK)。優化器會自動幫你找最快的路徑 (走 Index Scan)。絕對要避免使用沒有 Index 的一般欄位來算總筆數，這會引發不必要的 Full Table Scan。
