I am currently working with a system that operates on multiple clusters and when creating new records, it uses Sequences to generate the PKs. These are for generally smaller numbers of records at a time, however I am interested in expanding to handle larger batches of records that in addition to the smaller instances, but I would like to cut down on the number of calls required if possible.
I will say that I am aware that I can just do a batch insert and include the NEXTVAL call for the PK in that statement, however I am caching the PK for the duration of processing for other reasons so this will not suffice.
I currently have two thoughts on how to approach this:
1: Alter the sequence in a transaction, incrementing by the number of new rows, store the CURVAL in a temp variable, commit the transaction, and return the temp variable. I can assume that the range of CURVAL - # -> CURVAL are my reserved PKs. I have no idea how to implement this, but would have to assume a stored procedure would be involved. And that I would have one created per PK/ table. Concern is with how the transaction affects the sequence and how
2: Create a recursive CTE, using 0 as my base step and selecting those below the PKs needed. (Or -1 to prevent more than I need I think). I attempted the below:
WITH CTE AS (
SELECT NEXT VALUE FOR SEQ_#TABLE_NAME# AS PK, 0 AS STEP
SELECT NEXT VALUE FOR SEQ_#TABLE_NAME# AS PK, STEP+1 AS STEP
WHERE STEP < #NUM_ROWS#
SELECT T.PK FROM CTE AS T
My issue with this is the question of recursive CTE performance vs calling for individual values for the same amount. Not concerned with latency between app and db. At the very least I would not be interfering with other connections.
3: I have my two ideas, but I lack experience here. Are there any other suggestions in addition to critique?
do you need to know what the sequence number was for other processing? Such as inserting a foreign key into a subordinate table(s)? If not, I would suggest just using an identity column. No need to call for the sequence, just insert your next record and let the DB handle the numbering.
There is another key being used on all the tables that consists of a concatenated VARCHAR of unique identifiers from the parent table down to the table in question, however they are not used for determining the foreign key. I can't imagine using those in terms of a join on LIKE. Also, storing the key internally allows for additional error handling. I have tried playing with the system and it isn't easy or flexible to attempt a batch insert with a join on that key. This is from a system long before me.