Extra query getting triggered for fetching sequence next value - spring JPA

Question

I have a table with primary key column getting values from an oracle sequence. If I insert a record into my table, the best way would be -

INSERT INTO TABLE VALUES(SCHEMA.SEQUENCE.NEXTVAL, value1, value2, value3)

However, when I use save method from JPARepository, a select query for fetching only SCHEMA.SEQUENCE.NEXTVAL is run at time of object creation and then an additional INSERT query is run at end of transaction.

SELECT SCHEMA.SEQUENCE.NEXTVAL FROM DUAL

INSERT INTO TABLE VALUES(val_from_select_query, value1, value2, value3)

This is resulting in almost double time. Is there a way to configure my entity in such a way that it fetches sequence value in the insert query resulting in overall just 1 round trip to the DB?

Pistolnikus · Accepted Answer · 2020-09-07 09:16:44Z

1

What is the increment of the sequence? Because this happens only for the first row you're inserting within the increment boundaries.

Given the increment is 50, when you insert multiple values (even with delay) you would see:

SELECT SCHEMA.SEQUENCE.NEXTVAL FROM DUAL
INSERT INTO TABLE VALUES(val_from_select_query, value1, value2, value3)
INSERT INTO TABLE VALUES(val_from_select_query+1, value1, value2, value3)
INSERT INTO TABLE VALUES(val_from_select_query+2, value1, value2, value3)
...
INSERT INTO TABLE VALUES(val_from_select_query+48, value1, value2, value3)
INSERT INTO TABLE VALUES(val_from_select_query+49, value1, value2, value3)
SELECT SCHEMA.SEQUENCE.NEXTVAL FROM DUAL
INSERT INTO TABLE VALUES(val_from_select_query2, value1, value2, value3)
INSERT INTO TABLE VALUES(val_from_select_query2+1, value1, value2, value3)
...

I usually set increment to 50 by create sequence a_sequence_name start with 1 increment by 50. However this leads to some gaps in ids (1, 2, 3, 4, 5, 6, 7, 51, 52, 101) e.g. when restarting the application.

answered Sep 7, 2020 at 9:16

Pistolnikus

4964 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sarthak Over a year ago

I'll have to insert n records in table, based on the request I receive. So, I can't always increment by 50. Moreover, I put a loop and created 10 objects and called save for each of them. In this case, select and insert statements were still triggered 10 times.

Pistolnikus Over a year ago

You don't have to always increment by 50, it is just a setting of the sequence in the db. Hibernate will ask for the "current starting point" and then it knows it can fill next 50 ids, so it will not ask any more. You are saying that you put a loop to create 10 objects and the select was called 10 times - didn't you forget to change the DB sequence to increment by 50 (or any other number > 1)? This doesn't have to match your "batch" - as these things tend to be dynamic (e.g. differ for each request).

Mafor · Accepted Answer · 2020-09-07 08:43:03Z

0

With the ID generation strategy GenerationType.AUTO, JPA (or rather underlying ORM framework) needs to know the generated ID so it can set it on the created object. As the INSERT statement does not return the ID, an additional SELECT is necessary.

On Oracle 12 you can use GenerationType.IDENTITY which will result in a single statement:

INSERT INTO <TABLE> (...) values (?) RETURNING <PRIMARY_KEY> INTO ?

answered Sep 7, 2020 at 8:43

Mafor

10.9k2 gold badges27 silver badges39 bronze badges

Collectives™ on Stack Overflow

Extra query getting triggered for fetching sequence next value - spring JPA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related