Oracle 数据库高级 SQL 技巧

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

高级 SQL 技巧汇总[1]：

2. ROWID 利用

2.1 去重

-- 删除重复行
DELETE FROM employees WHERE ROWID IN (
  SELECT rid FROM (
    SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn
    FROM employees
  ) WHERE rn > 1
);

2.2 快速访问

SELECT ROWID, ... FROM employees;
-- 缓存 ROWID，快速 UPDATE
UPDATE employees SET ... WHERE ROWID = '...';

3. EXISTS vs IN

3.1 选择

-- IN：子查询小
SELECT * FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NY');

-- EXISTS：外查询小
SELECT * FROM employees e WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.id = e.dept_id AND d.location = 'NY'
);

3.2 NOT EXISTS vs NOT IN

-- NOT EXISTS：推荐（NULL 安全）
SELECT * FROM employees e WHERE NOT EXISTS (
  SELECT 1 FROM departments d WHERE d.id = e.dept_id
);

-- NOT IN：注意 NULL
SELECT * FROM employees WHERE dept_id NOT IN (SELECT id FROM departments);
-- 若子查询有 NULL，返回空

详细见：Oracle 子查询与 EXISTS。

4. 树形查询

4.1 CONNECT BY

SELECT employee_id, name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

4.2 排序

SELECT employee_id, name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY name;

4.3 函数

SELECT employee_id, name,
  SYS_CONNECT_BY_PATH(name, '/') AS path,
  CONNECT_BY_ROOT name AS root,
  CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

详细见：Oracle 层次查询。

5. 分析函数

5.1 累积

SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS cum_total,
  AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM sales;

5.2 排名

SELECT name, salary,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees;

5.3 偏移

SELECT sale_date, amount,
  LAG(amount, 1) OVER (ORDER BY sale_date) AS prev,
  amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS diff,
  (amount - LAG(amount, 1) OVER (ORDER BY sale_date)) / LAG(amount, 1) OVER (ORDER BY sale_date) AS growth_rate
FROM sales;

详细见：Oracle 高级分析函数。

6. MODEL 子句

SELECT year, region, sales
FROM sales_history
MODEL
  PARTITION BY (region)
  DIMENSION BY (year)
  MEASURES (sales)
  RULES (
    sales[2026] = sales[2025] * 1.1,
    sales[2027] = sales[2026] * 1.1
  );

7. MATCH_RECOGNIZE（12c+）

SELECT *
FROM stock_prices
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY price_date
  MEASURES 
    FINAL FIRST(up.price_date) AS start_date,
    FINAL LAST(down.price_date) AS end_date,
    FINAL COUNT(*) AS pattern_count
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO LAST down
  PATTERN (up+ down+)
  DEFINE 
    up AS up.price > PREV(up.price),
    down AS down.price < PREV(down.price)
);

详细见：Oracle SQL 模式匹配。

8. CTE 递归

WITH org_chart(id, name, mgr_id, lvl) AS (
  -- 锚
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归
  SELECT e.id, e.name, e.manager_id, oc.lvl + 1
  FROM employees e, org_chart oc
  WHERE e.manager_id = oc.id
)
SELECT * FROM org_chart;

详细见：Oracle CTE 与递归查询。

9. 分页

9.1 12c+ FETCH

SELECT * FROM employees ORDER BY id
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

-- 百分比
SELECT * FROM employees ORDER BY id
FETCH FIRST 10 PERCENT ROWS ONLY;

-- WITH TIES
SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 10 ROWS WITH TIES;

9.2 键集

-- 性能最佳
SELECT * FROM employees
WHERE id > :last_id
ORDER BY id
FETCH FIRST 10 ROWS ONLY;

10. UPSERT

-- MERGE
MERGE INTO employees e
USING (SELECT :id AS id, :name AS name FROM dual) s
ON (e.id = s.id)
WHEN MATCHED THEN UPDATE SET e.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

详细见：Oracle MERGE 语句详解。

11. RETURNING

-- DML 返回
INSERT INTO t VALUES (...) RETURNING id INTO v_id;

UPDATE t SET ... WHERE ... RETURNING col1, col2 INTO v1, v2;

DELETE FROM t WHERE ... RETURNING id BULK COLLECT INTO v_ids;

12. BULK

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees WHERE dept_id = 10;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = salary * 1.1 WHERE id = v_ids(i);
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

13. INSERT 多行

13.1 INSERT ALL

INSERT ALL
  INTO t1 (id, name) VALUES (id, name)
  INTO t2 (id, name) VALUES (id, name)
SELECT id, name FROM source WHERE ...;

13.2 条件

INSERT FIRST
  WHEN dept_id = 10 THEN INTO t_it VALUES (id, name)
  WHEN dept_id = 20 THEN INTO t_sales VALUES (id, name)
  ELSE INTO t_other VALUES (id, name)
SELECT id, name, dept_id FROM employees;

14. 子查询因子化

14.1 WITH

WITH 
  dept_stats AS (
    SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
    FROM employees GROUP BY dept_id
  ),
  high_paid AS (
    SELECT * FROM employees WHERE salary > 10000
  )
SELECT d.dept_id, d.cnt, d.avg_sal, COUNT(h.id) AS high_cnt
FROM dept_stats d, high_paid h
WHERE d.dept_id = h.dept_id
GROUP BY d.dept_id, d.cnt, d.avg_sal;

详细见：Oracle CTE 与递归查询。

15. 临时表

15.1 CTE

WITH temp AS (...)
SELECT ... FROM temp;

15.2 全局临时表

CREATE GLOBAL TEMPORARY TABLE gtt_emp AS
SELECT * FROM employees WHERE 1=0
ON COMMIT DELETE ROWS;  -- 或 PRESERVE ROWS

INSERT INTO gtt_emp SELECT * FROM employees;

16. 物化视图

-- 频繁查询
CREATE MATERIALIZED VIEW mv_summary
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
  AS SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

详细见：Oracle 视图与物化视图详解。

17. 外部表

CREATE TABLE ext_sales (
  id NUMBER,
  amount NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
  )
  LOCATION ('sales.csv')
);

18. 性能技巧

18.1 SQL 代替 PL/SQL

-- 差
FOR rec IN (SELECT * FROM t) LOOP
  INSERT INTO t2 VALUES (rec.id);
END LOOP;

-- 好
INSERT INTO t2 SELECT id FROM t;

18.2 减少 DISTINCT

-- 差
SELECT DISTINCT a.id FROM a, b WHERE a.id = b.id;

-- 好
SELECT a.id FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

18.3 UNION ALL

-- 好（无重复）
SELECT ... UNION ALL SELECT ...

详细见：Oracle SQL 查询优化技巧。

19. 常见坑与排错

19.1 笛卡尔积

- 忘 JOIN 条件
- 性能灾难
- 检查

19.2 NULL 处理

- = NULL：错
- IS NULL：对
- NVL/COALESCE

19.3 NULL IN

- NOT IN NULL：空
- NOT EXISTS：推荐

20. 最佳实践

CTE：清晰
MERGE：UPSERT
分析函数：复杂
FETCH：分页
BULK：批量
RETURNING：返回
EXISTS：NULL 安全
SQL 优先：高效
执行计划：验证
测试：完整

21. 参考资料

[1] Oracle Database SQL Tuning Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/