In our quest for ever-faster build times, we noticed that a large portion of our time running tests was getting the next OID for persisted objects.
After some investigation, we discovered the bottleneck. For every persisted object created in the system, a new connection was obtained from our datasource and a query was issued to retrieve a single OID. I know, I know… not very efficient. We wanted to refactor the code to grab batches of OIDs and cache them in memory.
How do we get groups of OIDs from an Oracle sequence? We had a couple of choices.
Modify the sequence’s INCREMENT BY to something larger than one.
We liked this approach because it allowed us to get a batch of OIDs with one query. But.. This approach has problems for external code that inserts data into our database. The external code would also have to know about the change in INCREMENT BY.
Changing all of the external integration code seemed like a big task. We wanted to find an approach that allowed us to keep the sequence unchanged, but allowed us to get a batch of OIDs with one query.
Query the sequence for a batch of OIDs
How do we do that? We need a query in Oracle that will generate N number of results. We tried several queries before we finally settled on one clever query. (Or at least it was clever to us.)
SELECT SEQ.nextval FROM (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= :1)
The clever part is the CONNECT BY LEVEL. This allows us to generate an arbitrary number of rows in our result set. The sequencer does the rest. We capture the results from the query and hand them out in memory. This query gave us the best of both worlds. We got to get a batch of OIDs with one query without having to change any of our external systems.
Where do we go from here?
In a word, GUIDs. This was a necessary performance related step on the way to implementing GUIDs.