Oracle 11g 複雜時間條件查詢優化：索引策略與 SQL 改寫

原 SQL 問題

SELECT ... FROM (
  SELECT * FROM table
  WHERE column10=0
    AND (column11 IS NULL OR column11 <= SYSDATE)
    AND (column12=100 AND (TO_CHAR(SYSDATE,'HH24') BETWEEN '08' AND '11'
         OR TO_CHAR(SYSDATE,'HH24') BETWEEN '14' AND '20'))
) WHERE ROWNUM < 100 ORDER BY column12, column13 DESC;

• TO_CHAR(SYSDATE,...) 導致無法使用索引範圍掃描。
• column11 IS NULL 亦阻礙索引使用。
• 嵌套子查詢 + ROWNUM 分頁，優化器難以下推謂詞。

優化建議

1. 消除函數列：將時間條件改寫為範圍比較。

   AND ( (TRUNC(SYSDATE) + 8/24 <= SYSDATE AND SYSDATE < TRUNC(SYSDATE) + 12/24)
      OR (TRUNC(SYSDATE) + 14/24 <= SYSDATE AND SYSDATE < TRUNC(SYSDATE) + 21/24) )
   

2. column11 設為非空預設值：DEFAULT DATE '1900-01-01'，查詢改為 column11 <= SYSDATE。
3. 創建組合索引：

   CREATE INDEX idx_table_10_11_12_13 ON table(column10, column11, column12, column13);
   

4. 使用 ROW_NUMBER() 分頁（12c+），或維持 ROWNUM 但確保外層 ORDER BY 列在索引前沿。

驗證

收集執行計劃（EXPLAIN PLAN），確認 INDEX RANGE SCAN、COUNT STOPKEY 出現，邏輯讀大幅下降。|