您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle SQL 查詢優化十大實戰技巧：從寫法到索引與綁定變數

編輯：Oracle 資料庫基礎

1. WHERE 子句過濾優於 HAVING

WHERE 在分組前過濾列，HAVING 在分組後過濾組。能用 WHERE 解決的絕不用 HAVING。

-- Bad
SELECT product_type_id, AVG(price)
FROM products
GROUP BY product_type_id
HAVING product_type_id IN (1,2);

-- Good
SELECT product_type_id, AVG(price)
FROM products
WHERE product_type_id IN (1,2)
GROUP BY product_type_id;

2. 表連結取代多重查詢

單次 JOIN 替代多次往返資料庫，減少網路交互與解析開銷。

3. 多表查詢使用完整限定列名

-- Good: p.description, p.price 明確指定來源表
SELECT p.name, pt.name, p.description, p.price
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id
  AND p.product_id = 1;

4. CASE 表達式合併多條統計查詢

SELECT COUNT(CASE WHEN price < 13 THEN 1 END) AS low,
       COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 END) AS med,
       COUNT(CASE WHEN price > 15 THEN 1 END) AS high
FROM products;

5. 索引建立原則

檢索行數 ≤ 總行數 10% 時索引最有效。
高選擇性欄位 (唯一值多) 適合建索引；低選擇性 (如性別、狀態碼) 效果差。
主鍵、唯一約束自動建立索引。

6. UNION ALL 取代 UNION

UNION 需排序去重，UNION ALL 直接合併，效能差異顯著。結果確無重複或可容忍重複時用 ALL。

7. EXISTS 取代 IN (子查詢)

-- Good: 半連結，找到一筆即停止
SELECT product_id, name
FROM products p
WHERE EXISTS (SELECT 1 FROM purchases pu WHERE pu.product_id = p.product_id);

8. EXISTS 取代 DISTINCT

-- Bad: 需排序去重
SELECT DISTINCT pr.product_id, pr.name
FROM products pr, purchases pu
WHERE pr.product_id = pu.product_id;

-- Good
SELECT pr.product_id, pr.name
FROM products pr
WHERE EXISTS (SELECT 1 FROM purchases pu WHERE pu.product_id = pr.product_id);

9. 綁定變數啟用游標共享

VARIABLE pid NUMBER
EXEC :pid := 1;
SELECT * FROM products WHERE product_id = :pid;
EXEC :pid := 2;
SELECT * FROM products WHERE product_id = :pid; -- 共享同一游標

注意：綁定變數屬 Session 級，重連需重新宣告。SQL 文字必須完全相同 (含大小寫、空白) 才能共享。

10. 避免在索引欄上使用函數/運算

-- Bad: 索引失效
WHERE TRUNC(create_date) = SYSDATE
WHERE salary * 12 > 100000

-- Good: 等價改寫保留索引
WHERE create_date >= TRUNC(SYSDATE) AND create_date < TRUNC(SYSDATE)+1
WHERE salary > 100000/12