Oracle 12c identity column skips chunk of numbers while inserting into table during ETL load

Question

Oracle 12c identity column skips chunk of numbers while inserting into table during ETL load.

I have below code in my ETL procedure which skips certain numbers while inserting into table during ETL process.

But if I execute below code manually I dont see any issue. All numbers seems to appear in sequential.

Code in my ETL procedure:-

MERGE INTO BRANDTABLE PT
USING (
    SELECT DISTINCT BRAND
        ,BRAND_DESC
    FROM SOURCE_BRAND
    ) TMP
    ON (PT.BRAND_CODE = TMP.BRAND)
WHEN MATCHED
    THEN
        UPDATE
        SET PT.BRAND_DESC = TMP.BRAND_DESC;

INSERT INTO /*+APPEND NOLOGGING*/ 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;

Table :-

CREATE TABLE TABLENAME
   (    "BRAND_ID" NUMBER(6,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 4085 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "BRAND_CODE" VARCHAR2(10 BYTE), 
    "BRAND_DESC" VARCHAR2(100 BYTE), 
     CONSTRAINT "DIM_BRAND_DESC_MASTER_PK" PRIMARY KEY ("BRAND_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABL"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABL" ;

Answer 1

Identity columns are nothing but an automated use of a sequence to populate the column. And oracle has never guaranteed gapless sequences. Any number of things can create a gap. Rollbacks of transactions that pulled a sequence; re-loading of the sequence cache before all cached numbers have been used. The fact that your gap picks up at '4060' (divisible by your cache size of 20) is strongly suggesting of a cache flush. Whatever, if your design depends on gapless sequences, the design is flawed. The first question would by why you think you need gapless sequences.