Skip Headers

# Sequencing and Preallocation Size

Previous

Next

To improve sequencing efficiency, TopLink lets you preallocate sequence numbers. Preallocation enables TopLink to build a pool of available sequence numbers that are assigned to new objects as they are created and inserted into the database. TopLink assigns numbers from the sequence pool until the pool is empty.

The preallocation size specifies the size of the pool of available numbers. Preallocation improves sequencing efficiency by substantially reducing the number of database accesses required by sequencing. By default, TopLink sets preallocation size to `50`. You can specify preallocation size either in TopLink Workbench or as part of the session login.

Preallocation size configuration applies to table sequencing and Oracle native sequencing. In Oracle native sequencing, the sequence preallocation size must match the Oracle sequence object increment size. Preallocation is not available for native sequencing in other databases as they use an auto-assigned sequence column. Oracle recommends that you use table sequencing in non-Oracle databases to allow preallocation.

For table sequencing, TopLink maintains a pool of preallocated values for each sequenced class. When TopLink exhausts this pool of values, it acquires a new pool of values, as follows:

1. TopLink accesses the database, requesting that the `SEQ_COUNT` for the given class (identified by the `SEQ_NAME`) be incremented by the preallocation size and the result returned.

   For example, consider the `SEQUENCE` table in Figure: TopLink Table Sequence Table. If you create a new purchase order and TopLink has exhausted its pool of sequence numbers, then TopLink executes a SQL statement to increment `SEQ_COUNT` for `SEQ_PURCH_ORDER` by the preallocation size (in this case, the TopLink default of `50`). The database increments `SEQ_COUNT` for `SEQ_PURCH_ORDER` to `1600` and returns this number to TopLink.

2. TopLink calculates a maximum and a minimum value for the new sequence number pool, and creates the pool of values.

3. TopLink populates the object sequence attribute with the first number in the pool and writes the object to the class table.

As you add new objects to the class table, TopLink continues to assign values from the pool until it exhausts the pool. When the pool is exhausted, TopLink again requests new values from the table.

Using TopLink Workbench, you specify a preallocation size when you choose a sequencing type at the project or session level. That preallocation size applies to all descriptors.

Using Java, you can specify a different preallocation size for each sequence that you create.

For more information about configuring preallocation size, see "Configuring Sequencing at the Project Level" or "Configuring Sequencing at the Session Level".