Oracle 12c Identity Column在ETL加载时出现断号问题求助

嘿，我之前处理过一模一样的场景，咱们来一步步拆解问题和解决方案：

首先，你的核心问题很明确：ETL存储过程里用带APPEND提示的INSERT往有Identity Column的表插数据时，BRAND_ID出现断号，但手动跑相同代码却能保持序号连续。

问题到底出在哪？ #

罪魁祸首就是你INSERT语句里的/*+APPEND NOLOGGING*/提示——它触发了Oracle的直接路径插入（Direct-Path Insert），而这种插入方式和Identity Column的缓存机制天生不对付：

• 你的表定义里BRAND_ID设置了CACHE 20，意思是Oracle会提前缓存20个连续的ID值，用来加速插入时的ID分配。
• 但一旦用了直接路径插入，Oracle会一次性把整个缓存的20个ID都消耗掉，哪怕你实际只插入了3条数据。剩下的17个ID就直接被丢弃了，下次插入时会从下一个缓存块（比如21开始）分配，自然就出现了断号。
• 手动执行的时候，要么是数据量太小Oracle没触发直接路径插入，要么是单次执行的缓存消耗没体现出来，所以ID看起来是连续的。

另外补充一句：如果ETL过程中存在事务回滚，也会导致Identity值被消耗（Oracle不会回滚已分配的Identity），但你说手动跑没问题，所以主要原因还是直接路径插入的缓存消耗。

怎么解决？ #

给你三个可行的方案，按需选择：

1. 拿掉APPEND提示（优先推荐，性能允许的话） #

如果你的ETL插入性能没有特别极致的要求，直接删掉APPEND提示，让Oracle用常规路径插入：

INSERT INTO BRANDTABLE ( BRAND_CODE ,BRAND_DESC )
SELECT DISTINCT BRAND ,BRAND_DESC
FROM SOURCE_BRAND SRC
WHERE NOT EXISTS (
    SELECT 1 FROM BRANDTABLE Trg WHERE Trg.BRAND_CODE = Src.BRAND
)
ORDER BY BRAND_DESC;

常规路径插入会按需分配Identity值，不会一次性吞掉整个缓存，这样ID就能保持连续了。

2. 把Identity Column改成NOCACHE #

如果必须保留APPEND的性能优势，又极度在意ID连续，可以把BRAND_ID的缓存设置改成NOCACHE：

ALTER TABLE BRANDTABLE MODIFY BRAND_ID GENERATED ALWAYS AS IDENTITY NOCACHE;

⚠️ 提醒一下：NOCACHE会降低插入性能，因为每次插入都要去数据字典拿ID，适合插入频率不高或者对连续性要求极高的场景。

3. 调整CACHE大小匹配ETL批次行数 #

如果既要APPEND的速度，又想尽量减少断号，可以根据ETL每次插入的平均行数调整CACHE大小。比如你每次ETL大概插15条数据，就把CACHE设为15：

ALTER TABLE BRANDTABLE MODIFY BRAND_ID GENERATED ALWAYS AS IDENTITY CACHE 15;

这样每次直接路径插入消耗的ID数和实际插入行数接近，断号的情况会少很多，但没法完全避免（比如某次插入行数小于缓存大小）。

验证方法 #

改完之后，你可以跑一下ETL，然后查一下ID的连续性：

SELECT BRAND_ID, BRAND_CODE, BRAND_DESC
FROM BRANDTABLE
ORDER BY BRAND_ID;

也可以查看Oracle自动生成的Identity序列的当前值，确认缓存的消耗情况：

SELECT LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME LIKE 'ISEQ$$_%'; -- 这个序列名是Oracle自动生成的，比如ISEQ$$_12345，对应你的BRANDTABLE

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