Oracle 数据库实战经验六则：HAVING、外连接、去重、事务、索引、重建

1. HAVING 子句精准用法

HAVING 对 GROUP BY 分组结果过滤，条件中仅允许常量、聚合函数或 GROUP BY 列，不可引用非分组非聚合列。

2. 外连接 (+) 替代 NOT IN 提升性能

低效写法：

SELECT A.EMPNO FROM EMP A
WHERE A.EMPNO NOT IN (SELECT EMPNO FROM EMP1 WHERE JOB = 'SALE');

高效改写（右外连接 + IS NULL）：

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 过滤，避免子查询全表扫描。

3. 删除重复记录的高效方案

小表（<50万行）可用 ROWID 法：

DELETE FROM TABLE_NAME A
WHERE ROWID < (SELECT MAX(ROWID) FROM TABLE_NAME
               WHERE COL1 = A.COL1 AND COL2 = A.COL2 ...);

大表建议：CREATE TABLE NEW_T AS SELECT DISTINCT * FROM OLD_T 后重建索引/约束/权限，或使用分区表 EXCHANGE PARTITION 技巧。

4. SET TRANSACTION 指定大回滚段规避 ORA-01555

SET TRANSACTION USE ROLLBACK SEGMENT ROLL_ABC;
DELETE FROM BIG_TABLE WHERE ...;
COMMIT;

提交后自动释放回滚段绑定，适合大批量删除/更新作业。

5. 索引使用黄金法则

• 驱动表返回行数 ≤ 总行数 2%~4% 时索引才有效。
• 频繁 DELETE 导致索引碎片，定期 ALTER INDEX ... REBUILD 或 TRUNCATE TABLE 重置。
• 复合索引遵循「最左前缀」原则，WHERE 条件须包含前导列。

6. IMP 重建数据库两阶段法避免视图依赖报错

-- 第一阶段：仅导入结构（rows=N）
IMP USER/PASS@TNS FILE=EXPDATA.DMP ROWS=N

-- 第二阶段：导入结构+数据，忽略已存在对象
IMP USER/PASS@TNS FILE=EXPDATA.DMP FULL=Y BUFFER=64000 COMMIT=Y IGNORE=Y

原理：先建所有对象骨架，再填数据，消除视图依赖底表未建的时序问题。