Oracle 排序進階技巧：中文多維排序、NULL 控制、ROWNUM 分頁優化與 12c+ 標準語法

本文為 93291 文章的姊妹篇，重點補充會話級 NLS 設置、性能優化原理、12c+ 標準分頁遷移指南，避免重複，建議配合閱讀。

一、會話級 NLS 設置：一次配置、全局生效

-- 設置中文拼音排序為默認
ALTER SESSION SET NLS_SORT = SCHINESE_PINYIN_M;
ALTER SESSION SET NLS_COMP = LINGUISTIC;

-- 驗證
SELECT * FROM nls_session_parameters WHERE parameter IN ('NLS_SORT','NLS_COMP');

-- 後續無需 NLSSORT 函數
SELECT * FROM perexl ORDER BY danwei;  -- 自動按拼音排序

• NLS_COMP=LINGUISTIC 讓比較操作（ORDER BY、WHERE col > '值'、BETWEEN、DISTINCT、GROUP BY）全部遵循 NLS_SORT 規則。
• 生產環境建議在連接池初始化腳本中設置，或通過 LOGON 觸發器自動應用。

二、排序性能優化原理

三、12c+ OFFSET/FETCH 遷移指南

-- 舊寫法 (三層 ROWNUM)
SELECT * FROM (
  SELECT ROWNUM rn, t.* FROM (
    SELECT * FROM personnel ORDER BY birth_date DESC
  ) t WHERE ROWNUM <= :offset + :limit
) WHERE rn > :offset;

-- 新寫法 (標準、可移植)
SELECT * FROM personnel
ORDER BY birth_date DESC
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY;

• 優勢：語法簡潔、優化器原生支援 Stopkey、跨數據庫兼容（PG/MySQL/SQL Server 同樣支援）。
• 注意：OFFSET 0 ROWS 等同於無 OFFSET；FETCH FIRST n ROWS WITH TIES 返回並列值（12.2+）。
• 遷移策略：新代碼直接用新語法；存量代碼按模塊重構，重點優化高頻分頁接口。

四、NULL 排序最佳實踐

-- 升序 NULL 最後（業務常見：未填寫排最後）
SELECT * FROM t ORDER BY col ASC NULLS LAST;

-- 降序 NULL 最後（業務常見：最高值優先，未填寫不干擾）
SELECT * FROM t ORDER BY col DESC NULLS LAST;

-- 避免舊寫法
-- ORDER BY NVL(col, '~')  -- 依賴字符集排序規則，不可移植
-- ORDER BY DECODE(col, NULL, 1, 0), col  -- 雙鍵排序，成本高

五、綜合案例：電商商品搜索排序

-- 需求：按相關度降序、銷量降序、價格升序、上架時間降序
-- 中文商品名拼音排序作為輔助維度
SELECT p.*, NLSSORT(p.name, 'NLS_SORT=SCHINESE_PINYIN_M') AS name_sort_key
FROM product p
WHERE p.status = 'ONLINE'
ORDER BY p.relevance_score DESC,
         p.sales_count DESC,
         p.price ASC,
         p.created_at DESC,
         name_sort_key
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

結語

Oracle 排序能力極強：會話級 NLS 設置簡化代碼、語言學索引消除排序操作、標準 OFFSET/FETCH 替代 ROWNUM 嵌套、NULLS FIRST/LAST 精確控制空值位置。建議團隊制定 SORTING_GUIDELINE：新表涉及中文排序必建語言學索引、分頁統一 OFFSET/FETCH、NULL 排序顯式聲明、遷移舊代碼納入技術債清理計劃。掌握這套體系，排序不再是性能瓶頸，而是業務邏輯的精確表達。