Why does Oracle not have autoincrement?

Asked

Viewed 144 times

4

Assuming that it is known that Oracle has the resources of the table quence.

I would like to understand why they only recently implemented the autoincrement in Oracle, since it was a feature existing in competitors (SQL Server, Mysql). What motivated the choice not to implement a feature autoincrement? This must have been debated a few times within the team, so there must be a logical reason.

I know there are ways to manually implement autoincrement, but I would like to understand what led them to relinquish the resource natively.

sql oracle pl-sql
  • 4

    Already possible in Oracle Database 12c Release 1.

  • So, if you can pass me the source, or some material to read about?

  • 1

    For a long time I have this doubt and never knew exactly why. Thanks that today we can count on the idendity.

  • Official link https://docs.oracle.com/database/121/DRDAA/migr_tools_feat.htm#DRDAA109

  • @Ismael is doubtful what was the advantage they had given up

  • Hard to know the real reason, probably not implemented because the concept of unique value for Pks and Fks is very abstract, I can make a VALOR_ANTIGO+1 like AUTO_INCREMENT as well as I can generate a UUID that achieve the same result.

  • For me the oracle has always sought to make everything very explicit that is being done, letting you really choose the value of the key and mouth the value in Current for example. In my head was a decision on this line.

  • Well, the fact is that Oracle did not leave this need without care, it is possible to achieve the same result with a Rigger and a very simple Quence.

  • 1

    I think only you. Lawrence Joseph Ellison could answer this, this type of key in general is one last modeling solution when one does not find a natural key , may come from the ancient "implication" of Oracle in relation to this.

Show 4 more comments

1 answer

2


From version 12c it is already possible to use this feature. Take a look at this link.

Now, the exact reason for not implementing before, would only be possible to obtain through some note of Oracle itself, but an interesting "justification" addressed by Gary Myers in a similar question is:

It may be just terminology. "AUTOINCREMENT" implies that a record 103 will be created between records 102 and 104. In an environment of clusters, this is not necessarily the case with sequences. A node can insert 100, 101, 102 while another node is inserting 110, 111, 112, so that the records are "out of order". (Of course the term Quence has the same implication.)

If you choose not to follow the Quence model, then you enter lock and serialization problems. You force an insert to wait commit/rollback of another insert before determining which is the next value, or you accept it, if the transaction fails, you will have a space between the keys.

Then there is the question of what you do if someone wants to insert a row in the table with a specific value for that field (ie, is permitted or works as a DEFAULT) or if someone tries update it. If someone enters 101, the autoincrement "jumps" to 102 or you risk trying duplicate values.

It may have implications for your IMP utilities and direct path and retroactive compatibility.

I’m not saying it couldn’t be done. But I suspect in Finally someone looked at it and decided they could spend time developing something better elsewhere.

In short, a Quence can do what an autoincrement does and something else, as well as give more freedom to the bank administrator to manipulate these values.

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.