7.7 Sequence number

---

Sequences (numbers) are used in many areas every day; from construction projects, to kitchen projects, to our high-tech computer applications. The construction worker has to follow a sequence when he constructs a house or a building. Similarly, when following a recipe to cook a food item we are required to follow a specific sequence to ensure the food is made the correct way.

In our high-tech computer applications, sequence numbers are used to keep the transactional sequence. They are used in multirow displays for the user interface to show data in a specific order. Additionally they are used for tracking the sequence of events in the case of a billing system or for tracking the sequence of events to be aired by a broadcasting center.

Developers create sequences within their application code to display information on the screen. They are stored as columns in tables or files to keep track of the next value or stored in memory as global variables. Applications that store the values in tables cause significant performance issues in a highly insert-intensive application. This is due to the fact that each time a new number is required the table has to be queried, the number changed and the new value updated. Applications that store these values in global memory variables could lose the values if the system crashed and applications have to determine the next value before starting again. One of the traditional methods of doing this is to scan all the tables to find the maximum values for the key.

Oracle uses sequence numbers inside its architecture in a number of areas; the system change number (SCN) being a prime example. DBAs should recognize the addition of the %S to the archive file names to create log sequence number.

A sequence is a sequential number generator that exists in a database. Some database vendors refer to a sequence as a serial, identity, or auto increment. The Oracle sequence generator reduces serialization where the statements of two transactions must generate sequential numbers at the same time. By avoiding the serialization that results when multiple users wait for each other to generate and use a sequence number, the sequence generator improves transaction throughput and, consequently, a user's wait will be considerably shorter. A sequence is useful for generating transaction-safe numbers for database transactions. The developer/user will have to query the appropriate sequence from the table DUAL. He/she does not have to worry about the additional logic for storing and retrieving of sequence numbers. The command could be simple, as:

SELECT JOB_HISTORY_SEQ.NEXTVAL FROM DUAL. 

In today's database design and development, we can take advantage of this feature by using it as surrogate keys in place of primary keys. Consider the example in Figure 7.2, the JOB HISTORY entity, which is based on a relationship between the JOB, EMPLOYEE and DEPARTMENT. Creating and maintaining the primary key (for uniqueness) due to the relationship is complicated and consumes a large resource. This complication could be even more significant in the case of a very large table with millions of rows. Most of the time all the columns that are part of the primary key are not used for query lookups.

---