SEQUENCE And NEXTVAL Oracle uses the concept of SEQUENCE to create numerical primary key values as we add rows of data into a table.Whereas numerical primary key population for My SQL and SQL Server is tied to individual tables, in Oracle the SEQUENCE construct is created separately and is not tied to an individual table.
As discussed in the documentation, a sequence in Oracle prevents duplicate values from being created simultaneously because multiple users are effectively forced to “take turns” before each sequential item is generated.
Brian is a software developer with over 21 years experience in creating and managing Internet-based software services.
For more information about creating auto-increment columns by using SQL Server and ADO.
NET, see Retrieving Identity or Autonumber Values and Creating Auto Increment Columns.
While the reasons for this design decision can only be guessed at, the good news is that even for users on older Oracle systems, there is a possible workaround to circumnavigate this pitfall and create your own auto incremented primary key column.
The first step is to create a in your database, which is a data object that multiple users can access to automatically generate incremented values.
You wouldn’t expect a sequence select to be a significant problem but recently we saw that it was—and in two different ways.
The issue came to light when investigating a report performance issue on an Oracle 18.104.22.168 non-RAC database.
In other words, a sequence can be used to generate primary key values for multiple tables, and the sequence continues even if it is being applied to a different table.