Oracle 日期範圍查詢兩大核心寫法與效能對比

前言

日期範圍查詢是業務系統最高頻 SQL 模式之一。Oracle 提供兩種直觀寫法：轉換為 DATE 型別比較與轉換為字串比較。本文深入分析兩者語義差異、索引使用條件、隱性轉換陷阱與最佳實踐。

一、兩種寫法對比

1.1 寫法一：轉換為 DATE 型別比較 (推薦)

SELECT *
FROM mytable
WHERE mydate < TO_DATE('2000-01-05', 'YYYY-MM-DD');

• mydate 欄位型別為 DATE 或 TIMESTAMP。
• 右側字串經 TO_DATE 顯式轉換為日期型別，雙方同型別比較。
• 可使用索引：若 mydate 有 B-tree 索引，優化器可做 INDEX RANGE SCAN。

1.2 寫法二：轉換為字串比較 (不推薦)

SELECT *
FROM mytable
WHERE TO_CHAR(mydate, 'YYYY-MM-DD') < '2000-01-05';

• 左側欄位經 TO_CHAR 轉為字串，右側為字串常數。
• 導致函數索引失效：除非建立 CREATE INDEX idx ON mytable (TO_CHAR(mydate, 'YYYY-MM-DD'))，否則全表掃描。
• 字串比較語義與日期不同：'2000-1-5' < '2000-01-05' (字串逐字元比較) 可能產生非預期結果。

二、隱性轉換陷阱

-- 寫法三：隱性轉換 (極度不推薦)
SELECT * FROM mytable WHERE mydate < '2000-01-05';

• Oracle 依賴 NLS_DATE_FORMAT 會話參數隱性轉換字串為日期。
• 若會話 NLS_DATE_FORMAT = 'DD-MON-RR'，字串 '2000-01-05' 解析失敗或解析錯誤。
• SQL 可移植性極差，跨環境極易出錯。

三、效能實測對比 (百萬筆測試表)

四、最佳實踐總結

1. 永遠使用顯式 TO_DATE / TIMESTAMP：WHERE mydate >= TO_DATE('2024-01-01','YYYY-MM-DD') AND mydate < TO_DATE('2024-02-01','YYYY-MM-DD')。
2. 採用半開區間 [start, end)：避免 BETWEEN 邊界歧義，利於分區裁剪。
3. 避免在 WHERE 條件欄位上加函數：TO_CHAR、TRUNC、EXTRACT 皆導致索引失效。
4. 若必須按月/日分組查詢：建立虛擬欄位 + 函數索引，或使用分區表 (RANGE BY DATE)。
5. 統一 NLS 環境：應用層連線池設定 NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS，但仍不可依賴隱性轉換。

五、進階：分區表日期範圍裁剪

CREATE TABLE sales (
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD')),
    PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01','YYYY-MM-DD')),
    ...
);
-- 查詢
SELECT * FROM sales WHERE sale_date >= TO_DATE('2023-05-01','YYYY-MM-DD')
                        AND sale_date <  TO_DATE('2023-06-01','YYYY-MM-DD');
-- 優化器自動 Partition Pruning，僅掃描 p_2023_q2