LAG()检测ID断层必须配ORDER BY id，否则结果不可预测；首行LAG(id)恒为NULL，差值为NULL不应COALESCE补0；重复id需加第二排序字段如created_date；缺号定位宜用LEAD()找缺口区间而非生成全量序列。
用LAG()检测ID断层，必须配ORDER BY
Oracle 12c+ 支持窗口函数，LAG() 是查缺失 ID 最直接的手段：它能拿到上一行的 id，再和当前行做差就能暴露缺口。但关键前提是必须显式写 ORDER BY id，否则 LAG(id) OVER () 的结果是不可预测的。
常见错误现象：没加 ORDER BY，查询返回零星几行或全空；或者差值列出现负数、乱序——这说明排序未生效，不是数据真连续，而是窗口“瞎算”。
- 首行的
LAG(id)恒为NULL，对应差值也是NULL，不用COALESCE补 0，否则会把首行误判为断点 - 若表有重复
id（比如重试插入），需加第二排序字段如ORDER BY id, created_date，否则LAG()可能跨重复值取错前驱 - 执行前确认
id列有索引，否则ORDER BY id会触发全表扫描，百万行表可能卡住
示例语句：
SELECT id, LAG(id) OVER (ORDER BY id) AS prev_id, id - LAG(id) OVER (ORDER BY id) AS gap FROM orders WHERE id - LAG(id) OVER (ORDER BY id) > 1;
用LEAD()定位缺口区间，避免生成全量序列
直接用 generate_series 或 CONNECT BY LEVEL 生成 1 到 MAX(id) 的序列，在 Oracle 中虽可行，但一旦 MAX(id) - MIN(id) 超过 10 万，CONNECT BY 就容易报 ORA-01436: CONNECT BY loop in user data 或内存溢出。更稳的做法是先用 LEAD() 找出所有缺口的起止边界，再按段生成。
使用场景：ID 范围大（如 1–500000）、但实际缺号少（仅几十处）。
-
LEAD(id) OVER (ORDER BY id)拿到下一行 ID，LEAD(id) - id - 1就是该缺口的长度 - 缺口起点是
id + 1，终点是LEAD(id) - 1；若结果为负或 NULL，说明无缺口或到末尾 - 空表时
MIN(id)/MAX(id)返回NULL，外层必须用COALESCE包裹，否则 CTE 报错
示例（找所有缺口区间）：
SELECT id + 1 AS missing_start, LEAD(id) OVER (ORDER BY id) - 1 AS missing_end FROM (SELECT id FROM orders ORDER BY id) WHERE LEAD(id) OVER (ORDER BY id) - id > 1;
Oracle 12c+ 用IDENTITY列时，缺失检测逻辑不变
即使 id 是 GENERATED ALWAYS AS IDENTITY，它也只是保证插入时自动赋值，并不保证值连续。删除、事务回滚、异常中断都会留下空洞。所以检测逻辑和普通整数主键完全一样——不能因为用了 IDENTITY 就跳过 LAG() 检查。
容易踩的坑：
- 误以为
IDENTITY= 连续，结果漏掉真实断层 - 对
IDENTITY列手动INSERT INTO ... VALUES (123, ...)后，后续NEXTVAL可能冲突，但检测脚本仍只看现有数据，不感知序列缓存状态 - 如果业务要求“ID 必须连续”，那
IDENTITY本身就不适用，得换用序列 + 触发器 + 应用层重试控制
补缺时别硬连全量序列，优先LEFT JOIN缺口区间
想把缺失 ID “填出来”，最常犯的错是写一大段 CONNECT BY LEVEL ，然后 NOT IN。这在 Oracle 中性能极差，且遇到 NULL 就失效。
正确做法：把上一步 LEAD() 算出的每个 (missing_start, missing_end) 区间，用递归 CTE 或分层查询展开成单个数字，再 LEFT JOIN 原表。
- Oracle 不支持
WITH RECURSIVE，得用CONNECT BY展开区间，但只对缺口段执行，不是全范围 - 例如某缺口是 102–105，就
SELECT 102 + LEVEL - 1 FROM DUAL CONNECT BY LEVEL ，比扫 1–100000 快两个数量级 - 若缺口跨越极大（如 1000000–1999999），别展开，直接记录区间即可——多数业务只需要知道“缺哪一段”，而非逐个列出
真正复杂的是“补缺”背后的业务含义：这个 ID 是否会被下游系统引用？补进去会不会引发外键冲突？这些都不是 SQL 能自动判断的，得人工核对。
