CODEWITHSUNDEEP
oracle-databasesequence
KAAMIRLI
KAAMIRLI

Reputation: 19

Large data sets.Squence value increment always 1

In this case always when run this sql statement SEQUENCE NUMBER INCREMENT BY 1.WHY INCREMENT EVEN NOT INSERT TO THE TABLES.

INSERT ALL WHEN ID = 23 THEN INTO ARTICLES
  (ID, NAME, DESCRIPTION)
VALUES
  (TEST.NEXTVAL, NAME, DESCRIPTION) WHEN ID > 100 THEN INTO BOOKS
  (ID, NAME)
VALUES
  (TEST.NEXTVAL, NAME)
  SELECT 98 as ID,
         'Mark Seemann' AS NAME,
         'DEPENDENCY INJECTION' as DESCRIPTION
    FROM DUAL

Upvotes: 0

Views: 41

Answers (2)

p3consulting
p3consulting

Reputation: 4640

And one of the solution is: wrap the TEST.NEXTVAL into a FUNCTION.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143033

That's because Oracle fetches the NEXTVAL before when conditions are being checked.

Supposing that ID = 50 (which is not 23, and is not larger than 100),

  • sequence gets its next value first
  • when is evaluated
    • the 1st condition isn't met
    • the 2nd condition isn't met
  • no rows are being inserted into any of two tables
  • sequence is, though, incremented

Although this is not the case in code you posted as it always inserts only one row per table, note that if there were two or more rows to be inserted - which actually might be the case as you mentioned "large data sets" (and if this code is simplified) - the sequence will be incremented only once for the whole insert all, which means that all rows you insert will share the same sequence value.

Upvotes: 2

Related Questions