Oracle 19c大表查询调优：解决表空间不足并快速获取结果

针对20亿条记录的employee表和10亿条记录的customer表，原NOT IN查询会生成大量临时中间数据，导致表空间耗尽。以下是具体调优和分批执行方案：

一、优化查询语句（替代NOT IN） #

1. 使用NOT EXISTS（推荐） #

NOT EXISTS的执行计划通常更高效，数据库会采用嵌套循环或哈希反连接，避免生成超大临时表，同时规避NOT IN在子查询包含NULL时的逻辑问题：

SELECT empid FROM employee e
WHERE NOT EXISTS (
    SELECT 1 FROM customer c
    WHERE c.custid = e.empid
);

2. 使用LEFT JOIN + IS NULL #

另一种等价写法，同样能减少临时数据生成：

SELECT e.empid FROM employee e
LEFT JOIN customer c ON e.empid = c.custid
WHERE c.custid IS NULL;

二、分批执行方案（核心解决表空间不足） #

1. 按empid范围分段查询（最优） #

假设empid是有序字段（如自增ID），将查询拆分为多个小范围，每次处理一部分数据，结果可直接导出到外部文件或追加到结果表：

-- 示例：每次处理1亿条empid范围的数据
SELECT empid FROM employee e
WHERE empid BETWEEN 1 AND 100000000
AND NOT EXISTS (SELECT 1 FROM customer c WHERE c.custid = e.empid);

-- 下一批调整范围
SELECT empid FROM employee e
WHERE empid BETWEEN 100000001 AND 200000000
AND NOT EXISTS (SELECT 1 FROM customer c WHERE c.custid = e.empid);

可通过脚本（Shell/Python/存储过程）自动遍历所有范围，避免手动重复操作。

2. 利用数据库导出工具直接输出结果 #

使用数据库自带的导出工具，将结果直接写入外部文件，完全不占用数据库表空间：

• MySQL示例（mysqldump）：

mysqldump -u 用户名 -p 数据库名 employee --where="NOT EXISTS (SELECT 1 FROM customer c WHERE c.custid = employee.empid)" --no-create-info --fields-terminated-by=',' > result.csv

• Oracle示例（expdp）：

expdp 用户名/密码@数据库 schemas=你的模式 tables=employee query='WHERE NOT EXISTS (SELECT 1 FROM customer c WHERE c.custid = employee.empid)' dumpfile=result.dmp logfile=export.log

3. 分页查询（适合非连续ID场景） #

若empid无规律，可结合ORDER BY和LIMIT/OFFSET分页，但注意大偏移量会导致性能下降，建议配合索引使用：

-- 先获取总范围
SELECT MIN(empid), MAX(empid) FROM employee;

-- 每次取100万条，逐步偏移
SELECT empid FROM employee e
WHERE NOT EXISTS (SELECT 1 FROM customer c WHERE c.custid = e.empid)
ORDER BY empid
LIMIT 1000000 OFFSET 0;

-- 下一页：OFFSET 1000000，依此类推直到无结果

三、关键前置优化 #

必须确保关联字段有索引，否则大表全表扫描会加剧临时数据生成：

-- 给employee.empid创建索引（若未存在）
CREATE INDEX idx_employee_empid ON employee(empid);

-- 给customer.custid创建索引
CREATE INDEX idx_customer_custid ON customer(custid);

内容的提问来源于stack exchange，提问作者yyy62103