Oracle查詢優化十大實戰技巧（上）：從連接符到DISTINCT的性能邏輯

前言：查詢優化的本質是「減少工作量」

SQL調優不僅是加索引、改執行計劃，更是在業務邏輯層面通過改寫減少數據庫引擎的處理工作量。本文精選10個高頻實戰技巧，前5個聚焦於結果集構建與邏輯簡化。

技巧一：|| 連接符構建豐富顯示列 —— 報告層邏輯下推

場景

員工基本信息表需生成「崗位 姓名 出生於 YYYY年MM月DD日」格式的顯示列，用於報表導出。

優化寫法

SELECT 
  job_title || ' ' || emp_name || ' 出生於 ' || 
  TO_CHAR(birth_date, 'YYYY"年"MM"月"DD"日"') AS emp_profile
FROM emp_basic_info;

核心價值

• 減少網絡傳輸： 單列傳輸替代多列，帶寬降低30%+；
• 業務邏輯集中化： 格式化邏輯在數據庫層完成，避免應用層（Java/Python/前端）重複實現；
• 可讀性提升： 報告直接可用，無需二次處理。

擴展：JSON構建（12c+）

SELECT JSON_OBJECT(
  'title' VALUE job_title,
  'name' VALUE emp_name,
  'birth' VALUE TO_CHAR(birth_date, 'YYYY-MM-DD')
) AS emp_json FROM emp_basic_info;

技巧二：DISTINCT 去重 —— 精準定義「業務唯一性」

反模式：應用層去重

# Python示例 - 低效
rows = cursor.fetchall()
unique_depts = set()
for row in rows:
    unique_depts.add((row.dept, row.job))

正規寫法

SELECT DISTINCT dept_name, job_title
  FROM emp_basic_info
 WHERE status = 'ACTIVE';  -- 關鍵：先過濾再去重，減少排序/哈希工作量

性能原理

• DISTINCT觸發HASH UNIQUE或SORT UNIQUE操作；
• 先WHERE後DISTINCT：過濾條件下推，減少參與去重的行數；
• 聯合索引支撐： CREATE INDEX idx_dept_job ON emp_basic_info(dept_name, job_title); 可實現INDEX FULL SCAN避免排序。

技巧三：WHERE條件下推 —— 降低數據訪問量的第一道防線

核心原則

「能在存儲層過濾的，絕不傳到服務層；能在索引層過濾的，絕不讀表塊。」

實戰對比

-- 低效：全表掃描後過濾
SELECT * FROM big_table WHERE UPPER(name) = 'ZHANG';

-- 高效：函數索引支撐
CREATE INDEX idx_big_name_upper ON big_table(UPPER(name));
SELECT * FROM big_table WHERE UPPER(name) = 'ZHANG';  -- INDEX RANGE SCAN

-- 更高效：業務協議約定存儲大寫，省去函數調用
SELECT * FROM big_table WHERE name = 'ZHANG';

進階：分區裁剪

-- 範圍分區表，查詢條件包含分區鍵
SELECT * FROM sales_part 
 WHERE sale_date >= DATE '2024-01-01' 
   AND sale_date <  DATE '2024-02-01';  -- 僅掃描2024年1月分區

技巧四：ROWMIT/ROWNUM分頁優化 —— 避免全量排序

經典陷阱

-- 低效：全表排序後取前N
SELECT * FROM (
  SELECT * FROM huge_table ORDER BY create_time DESC
) WHERE ROWNUM <= 20;

優化方案：索引順序掃描 + 早期停止

CREATE INDEX idx_huge_create_time ON huge_table(create_time DESC);

SELECT * FROM huge_table 
 WHERE create_time IS NOT NULL  -- 確保索引可用
 ORDER BY create_time DESC
 FETCH FIRST 20 ROWS ONLY;  -- 12c語法，自動轉為FIRST ROWS(n)優化

技巧五：WITH子句（CTE）模塊化複雜查詢 —— 可讀性與性能的雙贏

重構前：嵌套子查詢地獄

SELECT * FROM (
  SELECT dept_id, AVG(sal) avg_sal FROM (
    SELECT * FROM emp WHERE hiredate > SYSDATE - 365
  ) GROUP BY dept_id
) WHERE avg_sal > 5000;

重構後：CTE流水線

WITH recent_emp AS (
  SELECT /*+ MATERIALIZE */ * FROM emp WHERE hiredate > SYSDATE - 365
),
dept_avg AS (
  SELECT dept_id, AVG(sal) avg_sal FROM recent_emp GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_sal > 5000;

關鍵優化點

• MATERIALIZE Hint：強制物化CTE結果到臨時段，避免重複計算（適用於多次引用）；
• INLINE Hint：簡單CTE建議內聯，減臨時段開銷；
• 邏輯分層：過濾→聚合→篩選，每層可獨立驗證、調優。

總結：前五大技巧的性能影響力矩陣

下篇預告

後五大技巧將聚焦：分析函數替代自連接、Bind Variable Peeking應對、Hint精準干預、統計信息管理策略、SQL Plan Baseline穩定計劃，敬請期待。