萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 實戰經驗六則:HAVING 用法、外連接替代 NOT IN、刪除重複行、指定回滾段、索引碎片、重建避坑

1. HAVING 子句精準過濾分組

只能引用:常數、聚合函數、GROUP BY 欄位。
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;

2. 外連接 (+) 替代 NOT IN — 效能提升數量級

原寫法 (慢):

SELECT a.empno FROM emp a WHERE a.empno NOT IN
  (SELECT empno FROM emp1 WHERE job='SALE');

改寫 (快):

SELECT a.empno FROM emp a, emp1 b
WHERE a.empno = b.empno(+)
  AND b.empno IS NULL
  AND b.job = 'SALE';

原理:外連接保留左表所有行,右表無匹配則為 NULL,配合 IS NULL 過濾。避免 NOT IN 子查詢逐行比對 + NULL 值陷阱。

3. 刪除重複行 — 大表慎用 ROWID 相關子查詢

DELETE FROM table_name a
WHERE rowid < (SELECT MAX(rowid) FROM table_name
               WHERE col1=a.col1 AND col2=a.col2 ...);

**大表 (>50萬行) 效能極差**。替代方案:

  • CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table; 交換表名。
  • 或使用 ROW_NUMBER() OVER (PARTITION BY key ORDER BY rowid) > 1 標記刪除。

4. 指定大回滾段避免 ORA-01555

SET TRANSACTION USE ROLLBACK SEGMENT roll_abc;
DELETE FROM huge_table WHERE ...;
COMMIT;  -- 釋放指定回滾段

適用於長跑大批次刪除/更新。現代版本 (9i+) 建議改用 **自動 Undo 管理** + UNDO_RETENTION 保證。

5. 索引使用鐵律:選擇率 < 20% 才有效

  • 查詢返回行數 > 表總行數 20% → 優化器傾向 FTS。
  • 頻繁 DELETE 導致索引碎片 (空間不釋放) → 定期 ALTER INDEX ... REBUILD 或 COALESCE。
  • 允許條件下 TRUNCATE TABLE 可同時清除表與索引碎片。

6. 匯入重建資料庫:分兩階段避免視圖依賴失敗

# 階段 1:僅導入結構 (不含資料)
imp user/pass@sid file=exp.dmp rows=N

# 階段 2:導入結構+資料,忽略已存在物件
imp user/pass@sid file=exp.dmp full=Y buffer=64000 commit=Y ignore=Y

IGNORE=Y 允許物件已存在時繼續導入資料,解決視圖依賴基表順序問題。

copyright © 萬盛學電腦網 all rights reserved