Oracle 資料庫開發技術經驗談：SQL*Plus 技巧與效能優化（第一部分）

一、SQL*Plus 實用技巧

1. 查找與刪除重複記錄

-- 查重 (以 DRAWING, DSNO 為鍵)
SELECT DRAWING, DSNO FROM EM5_PIPE_PREFAB
WHERE ROWID != (
  SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
  WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING
    AND EM5_PIPE_PREFAB.DSNO = D.DSNO
);

-- 刪重 (保留最大 ROWID)
DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID != (
  SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
  WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING
    AND EM5_PIPE_PREFAB.DSNO = D.DSNO
);

2. 快速編譯所有視圖/授權/同義詞

資料庫遷移後視圖失效，利用 PL/SQL 批量產生 DDL：

SPOOL ON.SQL
SELECT 'ALTER VIEW ' || TNAME || ' COMPILE;' FROM TAB;
SPOOL OFF
@ON.SQL

同理：

SELECT 'GRANT SELECT ON ' || TNAME || ' TO USERNAME;' FROM TAB;
SELECT 'CREATE SYNONYM ' || TNAME || ' FOR USERNAME.' || TNAME || ';' FROM TAB;

3. 外部連接提升查詢速度

原寫法：

SELECT PAY_NO, PROJECT_NAME FROM A
WHERE A.PAY_NO NOT IN (SELECT PAY_NO FROM B WHERE VALUE >= 120000);

1 萬筆對 1 萬筆，NOT IN 需 1 億次比較，耗時 30 分鐘。改用外部連接：

SELECT A.PAY_NO, A.PROJECT_NAME
FROM A, B
WHERE A.PAY_NO = B.PAY_NO(+) AND B.VALUE < 120000;

縮短至 1 分鐘左右。

4. 觸發器中使用 :OLD / :NEW

• :OLD.COL：修改前的值
• :NEW.COL：修改後的值

範例：:OLD.DEPT_NO、:NEW.DEPT_NO。