Oracle 進階 SQL 與性能優化：分組聚合增強、子查詢分類、層次查詢、多表 DML、索引實戰與優化案例

分組聚合增強功能

ROLLUP / CUBE / GROUPING SETS

-- ROLLUP: 層次化小計 (總計 -> 分組小計 -> 明細)
SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP (dept_id, job_id);

-- CUBE: 所有維度組合小計 (全排列)
SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE (dept_id, job_id);

-- GROUPING SETS: 自定義聚合組合
SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS (
    (dept_id, job_id),  -- 明細
    (dept_id),          -- 部門小計
    (job_id),           -- 崗位小計
    ()                  -- 總計
);

-- GROUPING 函數識別小計行
SELECT 
    CASE WHEN GROUPING(dept_id) = 1 THEN 'ALL_DEPTS' ELSE dept_id END AS dept,
    CASE WHEN GROUPING(job_id) = 1 THEN 'ALL_JOBS' ELSE job_id END AS job,
    SUM(salary)
FROM employees
GROUP BY CUBE (dept_id, job_id);

子查詢分類與優化策略

標準子查詢 (非關聯)

• 嵌套子查詢：WHERE 子句中獨立執行，結果傳遞給外層
• 標量子查詢：返回單值，可用於 SELECT/WHERE/HAVING
• 優化：優化器可展開為 JOIN 或半連接

關聯子查詢

• 引用外層查詢列，對外層每行重新執行
• 優化器嘗試轉換為 JOIN 或使用緩存

EXISTS vs IN 優化

-- 推薦：EXISTS 適合外層大、內層小，有索引支持
SELECT e.ename FROM emp e
WHERE EXISTS (SELECT 1 FROM dept d WHERE d.deptno = e.deptno AND d.loc = 'NEW YORK');

-- IN 適合內層結果集小、可物化
SELECT e.ename FROM emp e
WHERE e.deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK');

關聯更新/刪除模式

-- 關聯更新
UPDATE table1 t1
SET t1.col = (SELECT expr FROM table2 t2 WHERE t2.key = t1.key);

-- 關聯刪除
DELETE FROM table1 t1
WHERE t1.col OPERATOR (SELECT expr FROM table2 t2 WHERE t2.key = t1.key);

層次查詢深度應用

核心語法回顧

SELECT [LEVEL], columns...
FROM table
[WHERE condition]
[START WITH root_condition]
CONNECT BY [NOCYCLE] PRIOR child = parent;

實戰：組織架構薪資統計

SELECT 
    e.empno, e.ename, e.mgr, e.sal,
    LEVEL AS lvl,
    SYS_CONNECT_BY_PATH(e.ename, ' > ') AS mgmt_chain,
    CONNECT_BY_ROOT e.ename AS top_mgr,
    CONNECT_BY_ISLEAF AS is_leaf
FROM emp e
START WITH e.mgr IS NULL
CONNECT BY PRIOR e.empno = e.mgr;

多表 DML 操作

無條件多表插入 (INSERT ALL)

INSERT ALL
  INTO emp_archive VALUES (empno, ename, sal, hiredate, 'ARCHIVE')
  INTO emp_high_sal VALUES (empno, ename, sal, 'HIGH_SAL')
SELECT empno, ename, sal, hiredate FROM emp WHERE sal > 3000;

條件多表插入 (INSERT ALL/INSERT FIRST)

-- ALL: 滿足所有條件的都插入 (一行可能插入多表)
INSERT ALL
  WHEN sal > 5000 THEN INTO exec_emp VALUES (empno, ename, sal)
  WHEN deptno = 10 THEN INTO dept10_emp VALUES (empno, ename, sal)
  WHEN job = 'SALESMAN' THEN INTO sales_emp VALUES (empno, ename, sal)
  ELSE INTO other_emp VALUES (empno, ename, sal)
SELECT empno, ename, sal, deptno, job FROM emp;

-- FIRST: 僅插入第一個滿足條件的分支 (互斥)
INSERT FIRST
  WHEN sal > 5000 THEN INTO exec_emp VALUES (empno, ename, sal)
  WHEN deptno = 10 THEN INTO dept10_emp VALUES (empno, ename, sal)
  ELSE INTO other_emp VALUES (empno, ename, sal)
SELECT empno, ename, sal, deptno, job FROM emp;

索引實戰：主鍵約束綁定索引

創建表時指定索引屬性

CREATE TABLE t_10 (
    id   NUMBER(6) NOT NULL PRIMARY KEY 
        USING INDEX (CREATE INDEX idx_t10_id ON t_10(id) TABLESPACE idx_ts),
    name VARCHAR2(50)
);

查詢索引信息

SELECT index_name, table_name, uniqueness, tablespace_name, status
FROM user_indexes
WHERE table_name = 'T_10';

SQL 優化經典案例：從全表掃描到索引範圍掃描

原始慢 SQL

SELECT SUM(sl0000) FROM xstfxps2 
WHERE dhao00 IN (
    SELECT dhao00 FROM xstfxps1 
    WHERE TRUNC(ywrq00) = TRUNC(SYSDATE) 
    AND khdm00 = '500000003913'
);

問題分析

• 子查詢對 xstfxps1 全表掃描 (無索引支持 TRUNC(ywrq00))
• IN 子查詢執行計劃可能選擇 FILTER 操作，外層表逐行執行子查詢
• TRUNC 函數導致 ywrq00 索引失效

優化步驟

1. 消除函數調用：改用範圍查詢

   WHERE ywrq00 >= TRUNC(SYSDATE) 
     AND ywrq00 < TRUNC(SYSDATE) + 1

2. 創建複合索引：

   CREATE INDEX idx_xstfxps1_khdm_ywrq ON xstfxps1(khdm00, ywrq00);

3. 改寫為 JOIN (優化器通常自動轉換)：

   SELECT SUM(p2.sl0000)
   FROM xstfxps1 p1
   JOIN xstfxps2 p2 ON p1.dhao00 = p2.dhao00
   WHERE p1.khdm00 = '500000003913'
     AND p1.ywrq00 >= TRUNC(SYSDATE)
     AND p1.ywrq00 < TRUNC(SYSDATE) + 1;

4. 驗證執行計劃：確認出現 INDEX RANGE SCAN、NESTED LOOPS 或 HASH JOIN

優化效果對比

核心優化原則總結

1. 避免列上使用函數/表達式：導致索引失效
2. 優先使用 JOIN 替代子查詢：優化器對 JOIN 優化更成熟
3. 複合索引列序遵循「等值優先、範圍其次、排序最後」
4. 統計信息及時收集：DBMS_STATS.GATHER_TABLE_STATS 保證優化器決策準確
5. 綁定變量使用：減少硬解析，複用執行計劃