Oracle 資料庫系統使用經驗六則：HAVING、外部聯結、SET TRANSACTION、索引維護與匯入遷移技巧

1. HAVING 子句正確用法

HAVING 用於過濾 GROUP BY 分組結果，條件中僅允許：常數、彙總函數、GROUP BY 欄位。不可直接引用未分組的明細欄位。

2. 外部聯結 (+) 替代 NOT IN 提升效能

(+) 置於「可能缺少資料」的那一方：

• WHERE a.col = b.col(+) → 左外部聯結 (保留 a 所有列)。
• WHERE a.col(+) = b.col → 右外部聯結 (保留 b 所有列)。

改寫範例：

-- 低效 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;

外部聯結可利用索引，避免 NOT IN 的全表掃描與 NULL 陷阱，大幅提升速度。

3. SET TRANSACTION 指定大型回滾段解決 ORA-01555

大量交易易遇 ORA-01555: snapshot too old，主因隨機分配的回滾段過小。解法：

SET TRANSACTION USE ROLLBACK SEGMENT roll_abc;
DELETE FROM table_name WHERE ...;
COMMIT;

指定足夠大的回滾段 roll_abc，COMMIT 後自動釋放指定。注意：自動 UNDO 管理 (AUM) 環境下建議改用 UNDO TABLESPACE 與 RETENTION GUARANTEE。

4. 使用索引的注意事項

• 選擇率原則： 子查詢/存取路徑應檢索 < 20% 表資料，超過則全表掃描更佳。
• 索引碎片： 頻繁 DELETE 導致索引空間無法重用，定期 REBUILD INDEX 或階段性 TRUNCATE TABLE (同步清除索引碎片)。

5. 匯入遷移避免視圖建立失敗：分兩階段

視圖依賴基礎表，IMP 依物件類型順序建立可能導致視圖先於基表建立而失敗。解法：

-- 階段 1：只導入結構 (無資料)
IMP jfcl/hfjf@ora1 FILE=expdata.dmp ROWS=N

-- 階段 2：導入結構與資料 (忽略已存在物件)
IMP jfcl/hfjf@ora1 FILE=expdata.dmp FULL=Y BUFFER=64000 COMMIT=Y IGNORE=Y

IGNORE=Y 確保第二階段不因物件已存在而中斷。

6. 大表匯出建議 COMPRESS=N

預設 COMPRESS=Y 會將所有 Extent 合併為單一 Initial Extent，匯入時若無連續大空間會失敗。超過 80MB 大表匯出請加 COMPRESS=N 保留原儲存特性。