Oracle CHAR 與 VARCHAR2 深度對比：存儲機制、比較語義、行遷移風險、DECODE/CASE 差異與選型決策樹

CHAR 與 VARCHAR2 是 Oracle 最基礎也最易誤用的字符類型。本文從底層存儲機制、比較語義差異、行遷移性能風險、函數/表達式處理差異、集合操作語義五個維度深度對比，並提供基於業務場景的選型決策樹，助您做出精準的類型決策。

一、存儲機制本質對比

二、比較語義核心差異：空格填充 vs 精確匹配

CREATE TABLE tt (c1 CHAR(2), c2 VARCHAR2(2));
INSERT INTO tt VALUES ('A', 'A');
INSERT INTO tt VALUES ('A', 'A ');
COMMIT;

-- 1. 字面量比較
SELECT * FROM tt WHERE c1 = 'A';      -- 2 行 (CHAR: 'A' 填充為 'A ')
SELECT * FROM tt WHERE c1 = 'A ';     -- 2 行
SELECT * FROM tt WHERE c2 = 'A';      -- 1 行 (VARCHAR2: 精確)
SELECT * FROM tt WHERE c2 = 'A ';     -- 1 行

-- 2. 列間比較
SELECT * FROM tt WHERE c1 = c2;       -- 1 行 (跨類型: 不填充，直接比較字節)

-- 3. DECODE (函數，視為 VARCHAR2)
SELECT DECODE(c1, 'A', 'Y', 'N') FROM tt;  -- N, N
SELECT DECODE(c1, 'A ', 'Y', 'N') FROM tt; -- N, N (DECODE 仍按 VARCHAR2)

-- 4. CASE (表達式，遵循類型語義)
SELECT CASE c1 WHEN 'A' THEN 'Y' ELSE 'N' END FROM tt;  -- Y, Y (CHAR 填充)
SELECT CASE c1 WHEN 'A ' THEN 'Y' ELSE 'N' END FROM tt; -- Y, Y

三、行遷移風險：VARCHAR2 更新的隱性代價

機制：VARCHAR2 列更新導致長度增長，若塊內剩餘空間不足 → 行遷移 → 索引指向原塊，原塊存轉發指針 → 額外 I/O 訪問新塊。

-- 監控行遷移
SELECT table_name, chain_cnt, avg_row_len
FROM dba_tables
WHERE owner = 'SCHEMA' AND chain_cnt > 0;

-- 解決：重組表
ALTER TABLE t MOVE TABLESPACE ts_data;
-- 或導出/導入、DBMS_REDEFINITION

決策：頻繁更新且長度變化大的列 → 改用 CHAR，以空間換穩定性，避免行遷移 I/O 放大。

四、集合操作 (UNION/INTERSECT/MINUS) 語義

CREATE TABLE t1 (name CHAR(10));
CREATE TABLE t2 (name CHAR(20));
INSERT INTO t1 SELECT TO_CHAR(LEVEL) FROM DUAL CONNECT BY LEVEL <= 5;
INSERT INTO t2 SELECT TO_CHAR(LEVEL) FROM DUAL CONNECT BY LEVEL <= 5;
COMMIT;

SELECT name FROM t1 MINUS SELECT name FROM t2;   -- 返回 5 行 (不相等)
SELECT name FROM t1 INTERSECT SELECT name FROM t2; -- 無返回

-- 結論：集合操作按「實際存儲字節」比較，CHAR 不填充空格參與比較

五、VARCHAR vs VARCHAR2：歷史包袱與標準差異

• VARCHAR 是 ANSI 標準類型，可存儲空字符串 '' (非 NULL)
• VARCHAR2 是 Oracle 專有類型，空字符串等同於 NULL
• 目前 VARCHAR 等同於 VARCHAR2 (同義詞)
• Oracle 保留未來使 VARCHAR 符合標準的權利
• 強烈建議：始終使用 VARCHAR2，避免未來遷移風險

六、選型決策樹：業務場景驅動類型選擇

1. 長度是否固定？
   • 是 (如：身份證 18 位、貨幣代碼 3 位、狀態碼 1 位) → CHAR
   • 否 → 進入 2
2. 是否頻繁 UPDATE 且長度變化大？
   • 是 → CHAR (避免行遷移)
   • 否 → 進入 3
3. 是否為主鍵/唯一索引列且定長？
   • 是 → CHAR (索引條目固定長度，分裂可控)
   • 否 → VARCHAR2 (默認選擇，節省空間)

七、最佳實踐清單

• [ ] 表列定義：默認 VARCHAR2(n CHAR)，按字符語義而非字節
• [ ] 固定代碼：CHAR(n CHAR)，明確語義
• [ ] PL/SQL 變量：錨定類型 table.col%TYPE，DDL 變更自動適配
• [ ] 比較邏輯：警惕 DECODE 對 CHAR 的 VARCHAR2 化處理，關鍵判斷用 CASE
• [ ] 索引策略：CHAR 列索引條目固定長，利於大表索引穩定
• [ ] 遷移檢查：SELECT * FROM dba_tab_cols WHERE data_type = 'VARCHAR' AND owner NOT IN ('SYS','SYSTEM') 排查 VARCHAR 殘留

八、總結

CHAR = 固定、填充、不遷移、索引穩、DECODE 視為 VARCHAR2、CASE 尊重本質。
VARCHAR2 = 變長、精確、可能遷移、空串即 NULL、標準 SQL 不兼容 VARCHAR。
選型鐵律：業務定長用 CHAR，業務變長用 VARCHAR2，頻繁更新用 CHAR，主鍵定長用 CHAR，其餘用 VARCHAR2。

將類型選型從「習慣性用 VARCHAR2」升級為「基於存儲模型、比較語義、更新模式、索引特性的工程決策」，是資深開發者與 DBA 的分水嶺。