Question

AutoNum increment on Oracle

1

Hi, I'm experiencing some strange behaviour of the incrementation of an AutoNum sequence on Oracle (modeler 4.5.1). We use an entity with an autonum attribute to generate a ticket id. Putted a new button on an overview screen of this entity and when I click this button, I see the autonum attribute incremented by 1. Good behaviour. However, if I use this entity somewhere in a microflow (before commit on a submit of a ticket) the autonum is incremented by 20. Not so good behaviour... To be sure to not have some other processes running, I added a before create microflow that simply shows a message. Nothing happens, until I submit the ticket. Does anyone recognize this behaviour or have a clue about what could be the issue? Thanks in advance! Arjan

asked 2013-10-07

Arjan de Lange

2 answers

Sebastiaan van den Broek · Answer 1 · 2013-10-07

I haven't seen this behavior, but note that autonumbers do not necessarily appear in a gapless sequence, if objects are created but then deleted it still means you can get a gap between numbers. There is no guarantee that numbers are in sequence, just that the next one is higher than the previous one and that they are unique.

Edit: It seems that Oracle itself does things in terms of reserving autonumber ids, which can then get lost in some situations. That at least explains the gaps you are having. I can think of some solutions for increasing a counter that don't involve retrieving the latest object every single time, but I'm not sure if we have any out-of-the-box solutions for this, I forwarded this question to someone in our Expert Services team.

Arjan de Lange · Answer 2 · 2013-10-08

Via Ask Tom I found that there is an option (CACHE_SIZE) on a sequence. By default set to 20 and has been introduced for performance reasons.

That at least explains the gap.

Another comment I found was to never use a sequence to enforce a gap free range.